TOP N with ALL DAX.

[Likhitha]

Hello All,

I'm having one small doubt , I applied ALL Filter in Caculating Sales.In matrix I projected ProductName and Sales and taken TOP 10 products based on sales(In Filter Pane).But with ALL Filter it's showing for all products not for TOP 10. I want 12,473,878 in Sales Rank Column.

How to sort it out?

[Mariusz · Accepted solution]

Hi @Likhitha 

You can use the below pattern replacing Brand with product Name

Top n Brank = 
VAR __topn = 
    TOPN(
        10,
        CALCULATETABLE(
            VALUES( 'Product'[Brand] ), 
            ALLSELECTED()
        ), 
        [Sales], --Measure,
        DESC
    ) 
RETURN
CALCULATE(
    [Sales], --Measure,
    __topn
)

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[V-pazhen-msft · Accepted solution]

@Likhitha 

If you want find the Top 10 product based on the sales, you could use RANKX to give a rank to the Sales Amount.  Then filter with <=10. Something like:

Calculate(Sum(), Filter(Table, [rank]<=10).

You can share a sample of your model, it is difficult to provide a absolute solution just with your description.

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

[Mariusz · Accepted solution]

Hi @Likhitha 

You can use the below pattern replacing Brand with product Name

Top n Brank = 
VAR __topn = 
    TOPN(
        10,
        CALCULATETABLE(
            VALUES( 'Product'[Brand] ), 
            ALLSELECTED()
        ), 
        [Sales], --Measure,
        DESC
    ) 
RETURN
CALCULATE(
    [Sales], --Measure,
    __topn
)

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[amitchandak]

For Rank Refer these links
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/Desktop/Top-N-and-All/m-p/606687

https://community.powerbi.com/t5/Desktop/TOP-N-in-DAX/m-p/860925