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 when I put my items into a matrix at the second level of rows (Brand --> Item), my Brand is still summing ALL of the items associated with that brand rather than just the TopN selection, even though my Items are only showing the TopN selected in my slicer.  Below is my current DAX measure that I am using.

 

I would have though the matrix would just sum up anything at the lower level and it would dynamically adjust to whatever TopN selection chosen, but it does not.

 

Any help would be greatly appreciated!

Thanks
Ryan 

 

 

TopN $ Flavors = 
VAR SelectedTop = SELECTEDVALUE('TopN'[TopN])
RETURN
SWITCH(TRUE(),
    SelectedTop = 0, [Total Sales ($)],
    RANKX ( 
            ALLSELECTED(  'Competitor Sales'[ITEM_DESCRIPTION] ),
            [Total Sales ($)]
                )
                  <= SelectedTop,
        [Total Sales ($)]
)

 

 

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests

10 Replies

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

       

       

  • ryan_b_fiting's avatar
    ryan_b_fiting
    Post Patron
    6 years ago

    v-alq-msftthat worked, I just changed the visual control to use my measure ranther than the RANK you included.

     

    Now, one last question branching off of this.....I have a trend chart by BRAND, but being I do not have Items as a dimension in the visual, it is back to summing up ALL sales rather than just TopN Item Sales.  Is there any work around for that by chance?

     

    Thanks 

    Ryan

    • v-alq-msft's avatar
      v-alq-msft
      Community Support
      6 years ago

      Hi, ryan_b_fiting 

       

      You may modify the 'Rank' measure as below.

       

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

       

       

       

      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-msft  thanks, but I get the same result as before. It does not sum up the TopN items by brand, but rather sums ALL items by brand for the specific date.  See below screen shot

        The way your TopN measure works is different from what I need (you are showing TopN overall, where I need TopN Items by Brand).  Other than that I followed your steps exactyl