Forum Discussion
TopN/RankX issues
I want to find Top 10 products. I want to check month wise sales of those top 10 product.
Top 10 products should be calculated dynamically I have to apply multiple filters on this(ex: location, category)
My ask is build the TopN visual level filter manually with help of DAX measures.
#PowerBI #Desktop #TopN # RankX #Filter
15 Replies
amitchandak Ahmedx Ritaf1983 Could you please share your views or could you please tag some other people. Thanks in advance
Hi, Anonymous
Can you provide sample data for testing? Sensitive information can be removed in advance. What kind of expected results do you expect? You can also show it with pictures or Excel. I look forward to your response.
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi v-zhangti
I could not share the data but can explain this in detail.Assume there is list of 20 products. I want to see top 5 products and total sales at month level.
sharing you the scrrnshot below for reference of output i am expecting.
TOP5 Products Month Year Products Jan-23 Feb-23 Mar-23 Apr-23 Total A 20 10 40 10 80 B 25 12 50 20 107 C 20 11 40 10 81 D 23 30 30 30 113 E 24 50 10 40 124 Total 112 113 170 110 505 This can be acheived easily by TOPN visual level filter but my requirement is to do it with DAX measures because I have to parametrize "N".
I have tried making it with TOPN and RANKX but when Month-Year column comes in picture it do not work correct.Hope this make problem clear.
Hi, Anonymous
You can try the following methods.
Rank = RANKX(ALL('Table'),CALCULATE(SUM('Table'[Sales]),ALLEXCEPT('Table','Table'[Products])),,DESC,Dense)Measure = IF([Rank]<=3,1,0)The Top 3 products are shown at this time.
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
