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

Anonymous
Not applicable

## Top 10 in matrix visual and month in columns

Hi everyone,

I am struggling again and hope you are helping me again. 🙂

I have a table with a quantity and already calculate top 10 of quantity for products. I can see top 10 produced produts for e.g. 2019.

But now, I would like to have top 10 produced products in matrix visualisation and the columns should display months.

This is how should like look in the end:

What I get is:

Does anybody has an idea how to get to desired solution??

Thanks a lot for your help!!!!!!!!!!!!!!

freiburgc

1 ACCEPTED SOLUTION
Super User

@Anonymous , both Top N and Rank will do

example :

Top 10 City Rank = CALCULATE([Sales],TOPN(10,all(Geography[City]),[Sales],DESC),VALUES(Geography[City Id]))

But then you have to this all measures you want to have in the matrix

like

CALCULATE([Measure in Mtraix],TOPN(10,all(Product[Product]),[YTD Sales],DESC),VALUES(Product[Product]))

6 REPLIES 6
Anonymous
Not applicable

I just saw that my target table wasn´t uploaded.

This is how it should look like:

Super User

@Anonymous , Create a measure for 2019. Maybe last year measure.

Got to the visual level filter of product and use that measure with TOP N (choose 10)

Anonymous
Not applicable

Hi @amitchandak ,

I did a mistake. I didn´t poste my target table.

This is how it should look like:

the data set is already limited to 2019.

Do you have an idea how to get there?

Thanks for you support!

freiburgc

Super User

@Anonymous , first or 2nd based on date selected

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))

and then put a visual level filter Product , Top 10 , YTD sales

refer Power BI — YTD Questions — Time Intelligence 1–5 // option without time intelligence is there.
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a

Anonymous
Not applicable

Hi @amitchandak ,

unfortunately it doesn´t work.

Do you have an idea how to make it work ranking is based on the total column?

At the moment ranking is based on every month.

Super User

@Anonymous , both Top N and Rank will do

example :

Top 10 City Rank = CALCULATE([Sales],TOPN(10,all(Geography[City]),[Sales],DESC),VALUES(Geography[City Id]))

But then you have to this all measures you want to have in the matrix

like

CALCULATE([Measure in Mtraix],TOPN(10,all(Product[Product]),[YTD Sales],DESC),VALUES(Product[Product]))

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.