Calculating Averages

[ajbogle]

Hi guys,

Stuck on trying to figure out a solution on calculating averages. Let me do my best to explain my dataset, I've attached a screenshot and blocked out any confidential information.

I want to focus on February and explain how our metrics work. I'm focusing soley on CPW which is Cost per Watt. The current calculation is as follows: [Total Payroll] / ([Total kW] * 1000)

Therefore, taking February's data $125,811 / (475.40 * 1000) = $0.26 which is 100% correct. However, I want to take an average of all the rows (i.e. 0.74, 0.09, 0.36, 0.24, 0, 0.27, 0.20, 0.29 = $0.27) and additionally, I want to do that again but removing the zeros which would come out to $0.31.

I was able to do this for the column total with the following measure:

CPW = AVERAGEX(KEEPFILTERS(VALUES('Crew Gusto'[Payroll Month Year])),CALCULATE(DIVIDE('Crew Gusto'[Crew Payroll],('Crew Gusto'[Crew kW per Gusto] * 1000))))

Total Numbers: $1,039,924 / ( 5999.94 * 1000) = $0.17 but with that measure it comes out to $0.24. I'd like to replicate that for each Row Subtotal.

Thanks!

[Hei_aQing]

Hi @ajbogle 

Would you mind simplifying your data and providing an example file? I'd like to try it.