I'm trying to calculate using Query Calculation the Hours worked divided by Total hours. For example: I have a sales rep with 7 hours and the total hours worked for every sales rep at this department is 50. these numbers appear in the same column with total on the bottom as usual.
([Sales Hours]/[Total(Sales Hours)])
What is happening is that both [Sales Hours] and [Total(Sales Hours)] equal 7, so I keep getting 1.
My columns look like this
Name Sales Hours Rate Query Calc
Sue 7 $40 1
Joe 24 $33 1
Frank 19 $38 1
Total 50 $111 1
I want the calculation to be 7/50, 24/50, and 19/50 down that column
Would anyone kindly help and let me know what I am missing?
Thanks,
Currently total is calculating just for each row, so you would need to change the calculation to:
( [Sales Hours] / Total( [Sales Hours] for report ) )
Or if there are multiple departments, and it should be total for each one, then:
( [Sales Hours] / Total( [Sales Hours] for [Dept] ) )
Thank You! That worked like a charm!
Best,
K
Hello again!
Looks like my client wants to multiply the total by the full report rate total. Can using multiple for options be combined in one statement? I keep getting an internal error trying to use what I learned in the last post.
I tried
( [Sales Hours] / Total( [Sales Hours] for [Dept] ) ) * Total([Sales Rate] for report )
and putting inside the parens.
( [Sales Hours] / Total( [Sales Hours] for [Dept] ) * Total([Sales Rate] for report ) )
Best,
K
What is [Sales Rate]? If that's the existing calculation, then I'd expect it to error because it's trying to calculate using itself.
Assuming [Sales Rate] = [Sales Hours] / total ( [Sales Hours] for [Dept] ) then you'd need to include both full calculations from above:
( [Sales Hours] / total ( [Sales Hours] for [Dept] ) ) * ( [Sales Hours] / total ( [Sales Hours] for report ) )
I think that's what you're saying, but if not let me know!
Hi seb,
Thank you for your response and help with this.
Sales Rate is actually the gross amount that sales rep made. Sales Hours is the amount of hours worked. I am trying to calculate two different columns together. I have tried a few things using the format you gave.
( [Sales Hours] / Total( [Sales Hours] for [Dept] ) ) * ( [Sales Hours] / Total( [Sales Hours] for report ) ) = This ran with no issues but not exactly what we need this time as this calculates using just the hours column.
( [Sales Hours] / Total( [Sales Hours] for [Dept] ) ) * ( [Sales Rate] / Total( [Sales Rate] for report ) ) = This gave an error when validating "SQL Created Circular reference"
I tried reversing the two.
( [Sales Rate] / Total( [Sales Rate] for report ) ) * ( [Sales Hours] / Total( [Sales Hours] for [Dept] ) ) = This gives another error when validating "Internal Error"
Best,
K
I don't think I'm fully understanding. Is this what you expect to see, using the example data from above?
Name | Sales Hours | Rate | Hrs/Total Hrs | Sales Rate | Rate/Total Rate | Hrs/Total Hrs * Rate/Total Rate |
Sue | 7 | $40 | 7/50 (0.14) | 740 | 740/50111 (0.014767217) | 0.00206741 |
Joe | 24 | $33 | 24/50 (0.48) | 2433 | 2433/50111 (0.048552214) | 0.023305063 |
Frank | 19 | $38 | 19/50 (0.38) | 1938 | 1938/50111 (0.038674143) | 0.014696174 |
Total | 50 | $111 | | 50111 | 50111/50111 | |