Aggregated Averages

[dmbd1904]

Hi, I'm having a lot of trouble with these measures.

Basically the measures are calculating the averages correctly at the row level, but not at the aggreagated 'Total'.

Inflated Base Rate / Unit (Ave.) =

VAR FXInflation = IFERROR(CALCULATE(MAX('FX Yearly'[data.value]))/CALCULATE(MAX(CapexDB[FX Base Year])),BLANK())

VAR CPIInflation = IFERROR(CALCULATE(MAX('CPI Yearly'[data.value]))/CALCULATE(MAX(CapexDB[CPI Base Year])),BLANK())

VAR REERInflation = IFERROR(CALCULATE(MAX('REER Yearly'[data.value]))/CALCULATE(MAX(CapexDB[REER Base Year])),BLANK())

VAR AveInflation = IFERROR(IF(OR(LASTNONBLANKVALUE(CapexDB[Region],"0")="ANZAC",LASTNONBLANKVALUE(CapexDB[Country],"0")="Argentina"),(FXInflation+REERInflation)/IF(OR(ISBLANK(FXInflation),ISBLANK(REERInflation)),1,2),(FXInflation+CPIInflation)/IF(OR(ISBLANK(FXInflation),ISBLANK(CPIInflation)),1,2)),BLANK())

VAR AdjAveInflation =IF(LASTNONBLANKVALUE(CapexDB[Country],"0")="Bermuda",AveInflation+0.035,IF(LASTNONBLANKVALUE(CapexDB[Country],"0")="Cayman Islands",AveInflation+0.0125,AveInflation))

var _table=(CALCULATE(AVERAGE(CapexDB[Base Rate to USD])))*CALCULATE(AVERAGE('Currency Changer'[Units per USD]),USERELATIONSHIP(CapexDB[Base Currency],'Currency Changer'[Currency code▲▼]))*AdjAveInflation

Return IFERROR(_table,BLANK())

Inflated Converted (Ave.) =

var _table=SUMMARIZE(CapexDB,CapexDB[Unit],CapexDB[Facility Level 1],CapexDB[Facility Level 2],CapexDB[Facility Level 3],"_value",CALCULATE([Inflated Base Rate / Unit (Ave.)])) return

SUMX(_table,[_value])

I would expect both averages to be 111,720.509

[v-kelly-msft · Accepted solution]

Hi @dmbd1904 ,

Try to use below measures instead:

Inflated Base Rate_ave=Averagex(values(Index),[Inflated Base Rate/Unit(Ave.)])

Inflated Coverted_ave=Averagex(values(Index),[Inflated Converted(Ave.)])

If the above measure still dont work out,pls upload your .pbix file to onedrive business and share the link with us,remember to remove the confidential information.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[VijayP]

@dmbd1904 

Use ISFILTERED Function

IF(ISFILTERED(Column), measure what you have created , average measure to get the overal average)

One more suggestion , Instead of using IFERROR in DAX start using Divide which is much better