cancel
Showing results for
Did you mean:
Frequent Visitor

## Total Average Measure based on parameters from another measure

The Measure is for each individual month is working correctly, but the total is calculating the average for every month.

I need to calculate the total average based on a criteria set by another measure.  Only average the months that have a value for the referenced measure.  I have used filters to exlude those values, I have use summarize and I still cannot get the correct total average.

WOEX =
//REFERENCE VALUE
var ttL = CALCULATE(SUMX(oge,OGE[Value]), FILTER(OGE, OGE[Cat] ="xloe" && OGE[Category] = "8/8THS TTL LEASE OPERATING EXP"))

// CALCULATE ONLY MONTHS THAT HAVE A VALUE FOR CORREPONDING REFERENCE VALUE
var surf = CALCULATE(AVERAGE(OGE[Value]), FILTER(oge, OGE[Cat] = "woe" && OGE[Category] = "SURFACE REPAIRS AND MAIN"&& ttl <> 0))

Return
CALCULATE(AVERAGEX(SUMMARIZE(OGE, Account_id[account_id]),surf), FILTER(OGE,ttl<>0))

Result Table:

268.77 is the average for all months. The correct answer I'm looking for is 358.36.

1 ACCEPTED SOLUTION
Frequent Visitor

I solved it.

For what ever reason the two measure where not seeing the realtionship to one another, so instead I created a calculated column for TTL and filtered the new measure to where TTL <> 0 and it corrected it.

2 REPLIES 2
Frequent Visitor

I solved it.

For what ever reason the two measure where not seeing the realtionship to one another, so instead I created a calculated column for TTL and filtered the new measure to where TTL <> 0 and it corrected it.

Super User II
WOEX =AVERAGEX(VALUES([Month]),
//REFERENCE VALUE
var ttL = CALCULATE(SUMX(oge,OGE[Value]), FILTER(OGE, OGE[Cat] ="xloe" && OGE[Category] = "8/8THS TTL LEASE OPERATING EXP"))

// CALCULATE ONLY MONTHS THAT HAVE A VALUE FOR CORREPONDING REFERENCE VALUE
var surf = CALCULATE(AVERAGE(OGE[Value]), FILTER(oge, OGE[Cat] = "woe" && OGE[Category] = "SURFACE REPAIRS AND MAIN"&& ttl <> 0))

Return
CALCULATE(AVERAGEX(SUMMARIZE(OGE, Account_id[account_id]),surf), FILTER(OGE,ttl<>0)))

Announcements