cancel
Showing results for 
Search instead for 
Did you mean: 

RPAVG & RPSUM - One not working without the other in Pivot

rvickersevotix
9 - Travel Pro
9 - Travel Pro

I Have the following measure that i'm trying to get to work.

RPAVG(
  (((([# of unique EntityReference],[DangerousOccuranceType_id])) +( ([# of unique Level1]) - ([# of unique Level1])))
   *200000) / 
 ( ([Employee Hours]) + ([Agency Hours]) + ([Contractor Hours]) +([Overtime Hours])  ),12)

I'm doing something a little funny with [# of unique level] because it seems to break if we have had zero [#of unique entityreference] so this is to just get around that issue.

However my RPAVG wont work unless I either somehow put RPSUM into the equation, or have another column using RPSUM within my pivot.

The current calc I have gives me the correct output but only when I have the second column where im doing rpsum / 12.

Screenshot_25.png

If I remove the rpsum / 12 column, It looks like this:

Screenshot_26.png

If I do just a basic RPAVG no funny business, this is the view I get:

RPAVG(
  (([# of unique EntityReference],[DangerousOccuranceType_id]) *200000) / 
 ( ([Employee Hours]) + ([Agency Hours]) + ([Contractor Hours]) +([Overtime Hours])  ) *1,12)

Screenshot_27.png

In this scenario, If I switch back on rpsum / 12, it gives me the correct output again.

Screenshot_28.png

 

I guess that I need help understanding if I'm using rpavg correctly, and If I am, why am I getting these different results.

Any help would be greatly appreciated.

0 REPLIES 0