Newby question on calculating

[kstevens67]

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,

[seb24c]

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] ) )

[kstevens67]

Thank You! That worked like a charm!

Best,

K

[kstevens67]

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

[seb24c]

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!

[kstevens67]

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

[seb24c]

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