Forum Discussion

naelske_cronos's avatar
naelske_cronos
Icon for Advocate II rankAdvocate II
11 months ago
Solved

Handle AverageX on different granularities matrix drilldown

Hello,   I have a matrix in Power BI where you can drill down to different granularities, from region on top to customer level (3 levels). The problem is that when drilling down, my averageX is no...
"DAX " "DAX help" 'Need help'
  • naelske_cronos's avatar
    naelske_cronos
    10 months ago

    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 regards

     

    Kevin

King7son1's avatar
King7son1
Frequent Visitor
11 months ago

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 ) .

 

also see this https://www.google.com/search?q=pragmatic+work+why+power+bi+totals+row+don%27t+work&oq=pragmatic+work+why+power+bi+totals+row+don%27t+work&gs_lcrp=EgRlZGdlKgYIABBFGDkyBggAEEUYOTIHCAEQABjvBTIKCAIQABiABBiiBDIKCAMQABiiBBiJBTIKCAQQABiiBBiJBTIKCAUQABiiBBiJBTIICAYQ6QcY_FXSAQkyMTM3NWowajGoAgCwAgA&sourceid=chrome&ie=UTF-8#fpstate=ive&vld=cid:910d3829,vid:KNkLIUIb2Lo,st:0 

 

 

naelske_cronos's avatar
naelske_cronos
Icon for Advocate II rankAdvocate II
11 months ago

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:

  1. #SumSales = Sum of Sales from FACT_SALES
  2. #SumForecast = Sum of Forecast from FACT_FORECAST
  3. #DivideAccuracy = Accuracy between #SumSales and #SumForecast
  4. #AverageAccuracyRegion = Average of Accuracy based on #DivideAccuracy to have on total levels this for the Region
  5. #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.

  1. 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.
  2. 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