Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
x-datita
Frequent Visitor

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.

xdatita_0-1715044228220.png

 

 

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 2
x-datita
Frequent Visitor

Thank you!

 

All measures work in a table but not in the matrix, do you know how can add these?

 

I tried as visual level filter and measure in visual, but it doesn't work

amitchandak
Super User
Super User

@x-datita , 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

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.