In metrics visual, how to make subtotal as dynamic average based on drill down date ?

[vaibhavd]

When the column header is selected as Month, provide average by month and user drill up to have quarter, the provide average by quarter.

Currently with following measure, I could see average by month but when I drill-up, average value should be the average of values displayed in columns.

My Maesure: 

Index =
VAR FactorTableByCustomer = SUMMARIZE(CUSTOMERS, CUSTOMERS[CUSTOMER_CD], "FactorCustomer", [Index ReportedIssue])
VAR FactorTableByMonth = SUMMARIZE(Dates, Dates[MonthInCalendar], "FactorMonth", [Index ReportedIssue])
VAR FactorTableByDay = SUMMARIZE(Dates, Dates[DayInCalendar], "FactorDay", [Index ReportedIssue])

RETURN
     IF(HASONEFILTER(Dates[DayInCalendar]),
           AVERAGEX(FactorTableByCustomer, [FactorCustomer]),
               IF(HASONEFILTER(Dates[MonthInCalendar]),
                  AVERAGEX(FactorTableByCustomer, [FactorCustomer]),
                       IF(HASONEFILTER(CUSTOMERS[CustomerBranch]),
                          AVERAGEX(FactorTableByMonth, [FactorMonth])
                      )
                 )
        )

CustomerBranch	Jul-19	Aug-19	Sep-19	Oct-19	Nov-19	Dev-19	Jan-20	Average
A	25	2	5	0	0	19	90	20.14
B	5	3	73	13	4	20	100	31.14
C	20	73	2	20	70	30	100	45
Average	16.7	26	26.7	11	24.7	23	96.7

CustomerBranch	Q3-19	Q4-19	Q1-20	Average	Expected Avg
A	10.7	6.3	90	20.14	35.7
B	27	12.3	100	31.14	46.4
C	31.7	40	100	45	57.23
Average	23.1	19.5	96.7

Please suggest - how can I achive this.

Thanks,

Vaibhav

[Anonymous]

Hello,

Will need more information on this to try helping out – what are examples of values you'd get from [Index ReportedIssue], [FactmorMonth] and [FactorCustomer]?

Also, it might be helpful to check out these articles about using SUMMARIZE() functions, could be possible that you're losing context because of how you have them currently structured.

https://www.sqlbi.com/articles/best-practices-using-summarize-and-addcolumns/

https://powerpivotpro.com/2017/12/summarize-addcolumns-arent-scary-can-see/

 Also also, instead of using HASONEFILTER(), maybe try using ISFILTERED() to see if that yields different results?

https://docs.microsoft.com/en-us/dax/isfiltered-function-dax

Shots in the dark but hopefully these will be helpful.

Thanks,
HI_IM_THIBBY