Matrix Visual: Count of Total Values One Filter Level Up

[hashari]

I have a Matrix Visual, for the sake of this example:

a) Highest Level before expanding is Region (there are 2 Regions)

b) Next level is District (there are 5 districts in a Region)

c) Lowest Level is Store (there are 10 stores in a district)

What I am trying to get to is a measure/column that counts the next level up.

So for example, if I :

i. expand my matrix all the way down to store level, I want to see a count of 50  (10 stores in a District * 5 Districts)

ii. expand my matrix down to the district level, I want to see a count of 100 (10 stores in a District * 5 Districts * 2 Regions)

Not sure where to go from here.

[v-kelly-msft · Accepted solution]

Hi  @hashari ,

Create a measure as below:

Measure = 
var _store=CALCULATE(DISTINCTCOUNT('Table'[store]),FILTER(ALL('Table'),'Table'[district ]=MAX('Table'[district ])&&'Table'[Region ]=MAX('Table'[Region ])))
var _district=CALCULATE(DISTINCTCOUNT('Table'[district ]),FILTER(ALL('Table'),'Table'[Region ]=MAX('Table'[Region ])))
var _region=CALCULATE(DISTINCTCOUNT('Table'[Region ]),ALL('Table'))
Return
IF(ISINSCOPE('Table'[store]),_store*_district,IF(ISINSCOPE('Table'[district ]),_store*_district*_region,COUNTROWS('Table')))

And you will see:

I made a simple sample for reference,see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[amitchandak]

@hashari , You can use TOP N filters

https://www.sqlbi.com/articles/filtering-the-top-3-products-for-each-category-in-power-bi/

Also, refer Rank for this
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/3...

[hashari]

@ amitchandak  -- I looked through a few of those examples, but not sure if I am connecting the dots properly.

I guess I need a little more help to reach my desired endpoint.