cancel
Showing results 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

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.

I hope you can help me. I share you my pbix file

Thank you! :))

2 REPLIES 2
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

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

Announcements

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 Monthly Update - June 2024

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

New forum boards available in Real-Time Intelligence.

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

Top Solution Authors
Top Kudoed Authors