Forum Discussion

ryan_b_fiting's avatar
ryan_b_fiting
Post Patron
6 years ago

TopN Dynamic Filter in Matrix

Hello Community -    I am in need of some help with my TopN slicer and how it interacts with a Matrix.  I have a measure that is summing my TopN sales amounts by Item which is working perfect.  But...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
v-alq-msft's avatar
v-alq-msft
Community Support
6 years ago

Hi, ryan_b_fiting 

 

Based on your description, I created data to reproduce your scenario.

Table:

 

You may create a what-if parameter from 1 to 5 and measures as below.

 

Rank = 
RANKX(
    ALLSELECTED('Table'),
    CALCULATE(SUM('Table'[Sales]))
)

TopNValues = 
var _topn = 'TopN'[Parameter Value]
return
SWITCH(
    TRUE(),
    _topn = 0,SUM('Table'[Sales]),
    [Rank]<=_topn,SUM('Table'[Sales])
)

visual control = IF([Rank]<=SELECTEDVALUE('TopN'[TopN]),1,-1)

 

 

Finally you may put the measure 'visual control' in the visual level filter and then get the result.

 

Best Regards

Allan

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

  • ryan_b_fiting's avatar
    ryan_b_fiting
    Post Patron
    6 years ago

    v-alq-msftthanks for this, I will test it out.  But before I do I want to ask, I am looking to have TopN Items for each brand, is this doing that?  Or is this just taking the TopN and disbursing it based on the ranking?  

     

    So if I select Top 5, I want to see the top 5 items in each brand and have the sum of the brand in the matrix equal the 5 items that re in the top for each brand.