Total Average Measure based on parameters from another measure

[Anonymous]

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.

[Anonymous · Accepted solution]

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.

[wdx223_Daniel]

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