Forum Discussion
matrix with top n
Hello
I have a matrix where the columns are stores, the rows are collections and the values are sales.
I am looking for a dynamic stores ranking. That is, if I write in the filter a top ‘2’, it should show the two best selling stores and then see how their sales are distributed by collection. But it doesn't work!
In this image, if we write top 2, it should show only shops 5 and 4, because they are the first two in sales and the matrix shows more.
I hope you can help me. I share you my pbix file
https://drive.google.com/file/d/137SC6UYQ7MyLM0uIhbv95CTMrrsCfARn/view?usp=sharing
Thank you! :))
2 Replies
- amitchandak
Super User
Anonymous , First have measure that only use store level total
Store Measure = calculate([Meausre] , filter(all(Table), Table[Store] =max(Table[Store]) )
or
Store Measure = calculate([Meausre] , removefilters(Table[Collection]) )
Now have a rank on this and use that as visual level filter
Rank = Rankx(allselected(Table[Store]), [Store Measure])
or use top N, and use this measure in visual
calculate([Measure],keepfilters(TOPN(selectedvalue(TOPN[Value]) , allselected(Table[Store]), [Store Measure], desc)))
You can also consider Window or new Rank function
Power BI - New DAX Function: RANK - How It Differs from RANKX: https://youtu.be/TjGkF44VtDo
Dynamic TOPN using TOPN/Window and Numeric parameter: https://youtu.be/vm2mdEioQPQ
