Forum Discussion

dmbd1904's avatar
dmbd1904
Icon for Helper III rankHelper III
6 years ago
Solved

Aggregated Averages

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

 

Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    6 years ago

    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!

2 Replies

  • v-kelly-msft's avatar
    v-kelly-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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's avatar
    VijayP
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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