Average of summary

[Rui_Reis]

Hello, guys! I'm creating a measure where I summaryze 3 kpi's from the same data base differently, for 2 of them is quite simple, but when it comes to the last one, I'm not finding a good way to summarize it. The WD are summarized as sum when they are from the same month and different locations, but when I summaryze the months, should do an average.

I tried to create a virtual table summarizing as a sum from the original DB and summarize again as an average from the virtual table, but it doesn't work and I feel like this is happening because my slicers are filtering the original DB.

Do you have a better way to do it or can you guide me what I'm doing wrong here?

Code:

KPI 1 =

VAR table1=SUMMARIZE(FILTER(Data_Base,Data_Base[KF]="DR"),Data_Base[Year],Data_Base[Date],Data_Base[KF],"DR",SUM(Data_Base[Value]))

return

IF(

    SELECTEDVALUE('KPI I'[KF])="DR",

    CALCULATE(AVERAGEX(table1,[DR]),Data_Base[PO]=SELECTEDVALUE('KPI I'[PO]),Data_Base[Year]=SELECTEDVALUE('KPI I'[Year])),

    IF(OR(SELECTEDVALUE('KPI I'[KF])="Fleet",SELECTEDVALUE('KPI I'[KF])="COIN"),

        CALCULATE(SUM(Data_Base[Value]),Data_Base[Date]=MAX(Data_Base[Date]),Data_Base[PO]     =SELECTEDVALUE('KPI I'[PO]),Data_Base[Year]=SELECTEDVALUE('KPI I'[Year]),Data_Base  [KF]=SELECTEDVALUE('KPI I'[KF])),

        CALCULATE(SUM(Data_Base[Value]),Data_Base[PO]=SELECTEDVALUE('KPI I'[PO]),Data_Base[Year]=SELECTEDVALUE('KPI I'[Year]),Data_Base[KF]=SELECTEDVALUE('KPI I'[KF]))))

[v-shex-msft · Accepted solution]

HI @Rui_Reis,

For the multiple level aggregation, you should consider using SUMMARIZE function and iterator functions(e.g. SUMX, AVERAGEX...) to apply different level aggregations.

Measure Totals, The Final Word 

Also, you can use ISINSCOPE or ISFILTERED functions to check the different level of row context level and apply different type of calculate expressions.

Use IsInScope to get the right hierarchy level in DAX - Kasper On BI

Regards,

Xiaoxin Sheng