TopN Dynamic Filter in Matrix

[ryan_b_fiting]

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 ($)]
)

[ryan_b_fiting]

@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]

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]

@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

[Ashish_Mathur]

Hi,

I cannot spot any mistake in the table there.  I see the Top 4 items per brand and the total (112,164.73) seems to be the summation of only those 4 items.  What is the problem?

[ryan_b_fiting]

@Ashish_Mathur It branched off into another question from the original Matrix issue which I then posted the issue here:

https://community.powerbi.com/t5/Desktop/TopN-Only-SUM-TopN-Items-for-Category-Total/m-p/1018556

[ryan_b_fiting]

@Ashish_Mathuryou are correct, the matrix is working perfectly.  The issue comes with the trending line graph.  That is by brand (items are not a dimension there).  But I want the values in the line graph to reflect only the sum of the TopN items associated with the brands. So I would expect all data points for the top brand in the line chart to total the 112,164.73, but they do not.

[v-alq-msft]

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]

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

[amitchandak]

@ryan_b_fiting ,

Refer if this can help

https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/3...

[Greg_Deckler]

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490