Forum Discussion
Advocate IIHandle AverageX on different granularities matrix drilldown
Hello,
The question has not been resolved but I have found a workaround using bookmarks and seperate measures for each level of hierarchy. I guess the problem lies in the hierarchy context for the totals for which a single measure is "impossible" to use.
Kind regardsKevin
Advocate IIHello,
Well I tried this DAX code on my example that I showed instead of the complex one:
ExcelStyle =
VAR RowIsDetail = ISINSCOPE(SalesPerson[Key])
VAR region = ISINSCOPE(Region[Region])
VAR ColIsDetail = ISINSCOPE('Date'[Month])
RETURN
SWITCH(
TRUE(),
-- DETAIL CELL: salesperson + month → show sum
ColIsDetail && (RowIsDetail || region),
[SumValue],
-- ROW SUBTOTAL (region) → show average
NOT RowIsDetail && ColIsDetail,
average('FACT'[Value]),
-- COLUMN TOTAL (month total row) → show average
RowIsDetail && NOT ColIsDetail,
average('FACT'[Value]),
-- GRAND TOTAL (neither in scope) → show average
average('FACT'[Value])
)but the problem is that the total does not know when to use regional level or sales person level because there is no filter context on total row, so still no luck...
Kind regards
Kevin
naelske_cronos to be honest I am truly confused about the requirements.
It is hard to provide assistance when you are changing from one model design to another in your responses
Do you need to need a weighted average based on the logic shown in your _DivideAccuracy measure or just a straight average (ex, Divide(fActual, fForecast,0) ?
Are building from one fact table or two? (perhaps you should provide a screen shot of model view or even better create measures to document you model and share that (INFO.VIEW.COLUMNS function (DAX) - DAX | Microsoft Learn) as well as some sample data.
INFO.VIEW.MEASURES()INFO.VIEW.COLUMNS()
INFO.VIEW.RELATIONSHIPS()
Also confirm the hieracy for your visual. You only need to use insocpe for the items on your hierachy, Regison SalesPerson (the fields you drill into ) .
- naelske_cronos
Hello,
My apologies forget about the other models to prevent confusion. This is a link to the PBIX: pbi_tests/Average Subtotals Drilldown.pbix at main · kevinn1992/pbi_tests · GitHub
It contains 5 measures:- #SumSales = Sum of Sales from FACT_SALES
- #SumForecast = Sum of Forecast from FACT_FORECAST
- #DivideAccuracy = Accuracy between #SumSales and #SumForecast
- #AverageAccuracyRegion = Average of Accuracy based on #DivideAccuracy to have on total levels this for the Region
- #AverageAccuracySalesPerson = Average of Accuracy based on #DivideAccuracy to have on total levels this for the SalesPerson
The tables on the left are self-explanatory but the two tables on the right is what is my problem.
- Table 1 right is a drilldown on Region level and uses the measure #AverageAccuracyRegion to have an average of the accuracy on total level for example January => (83,64% + 73,96% + 9,11%) / 3 = 55,57% like a simple average in EXCEL.
- Table 2 right is a drilldown on SalesPerson level and uses the measure #AverageAccuracySalesPerson to have an average of the accuracy on total level for example January => (16,91% + 71,10% + 91,25% + 73,96% + 9,11%) / 7 = 52.47% like a simple average in EXCEL.
I need two separate matrices and two seperate measures to change my subtotals depending on the hierarchy level, region or salesperson (there is also a third one called productgroup but I first want to make it work with these two):
The question is: how do I only use one matrix and one measure where it changes the average accuracy depending on the current hierarchy level? Because the subtotals on column level does not know what the current scope is (region or salesperson)
Maybe some side information. Row subtotals are disabled for all the hierarchy levels except region.
I hope that this explains a lot?
Kind regards
Kevin