none
Sum of data and grand totals not adding up RRS feed

  • Question

  • I need some help with a fairly complex formula. I am trying to figure out which hours of the day I am paying the most in labor. I run a restaurant and want to compare my labor costs in the 1:00PM hour to the sales in the same hour, etc. Here's what I've got for a formula:

    CalcHours:=sumx(Timesheets,round(countrows(filter(TimeTable,[Time]>=Timesheets[Start] && [Time]<=if([End]<timevalue("2:00 AM"),timevalue("11:59 PM"),Timesheets[End]-(1/24/60))))/60,2))

    TimeTable is a simple table with every minute of the day and which hour of the day it falls under, and I count the rows to see how many rows in that table are between the start and end time. Then it is plotted out on a table with the calculated hour from that table across the columns. I get this:

    CalcHours Column Labels
    Row Labels 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 Grand Total
    2019
    March $0.05 $1.01 $3.77 $2.42 $4.33 $14.99 $7.54 $0.07 $1.69 $1.35 $4.29 $10.19 $5.25 $4.56 $0.50 $949.09
    April $0.02 $2.20 $3.99 $6.38 $14.45 $11.41 $31.45 $4.04 $5.82 $6.86 $15.21 $43.70 $20.28 $17.60 $1.00 $1.68 $2,512.07
    May $0.22 $1.83 $1.89 $2.93 $4.69 $8.40 $29.80 $4.79 $3.23 $31.62 $14.26 $13.72 $14.32 $3.90 $0.42 $2,030.58
    June $1.02 $3.22 $2.12 $10.99 $3.17 $17.82 $18.91 $3.37 $1.96 $27.60 $7.90 $13.86 $16.17 $3.21 $0.52 $1,804.09
    July $0.02 $0.72 $2.00 $3.05 $4.17 $6.95 $0.53 $0.66 $2.65 $1.64 $5.37 $1.28 $0.67 $418.47
    Grand Total $0.24 $5.12 $10.83 $15.20 $34.55 $30.36 $98.23 $42.23 $13.02 $11.17 $78.43 $71.79 $63.42 $54.62 $13.34 $3.12 $7,714.30


    The grand totals are correct, but the individual numbers are not. What am I doing wrong here?

    Monday, July 29, 2019 3:35 PM

All replies