cancel
Showing results for
Search instead 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)))  ## Helpful resources

Announcements #### Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group #### The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings. Top Solution Authors
Top Kudoed Authors
Users online (10,099)