Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Matrix Visual: Count of Total Values One Filter Level Up

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.

Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    6 years ago

    Hi  Anonymous ,

     

    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!

3 Replies