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
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:

Unbenannt_Prodstatistik_Problem.png

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

 

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

freiburgc

 

 

 

 

1 ACCEPTED SOLUTION

@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]))

 

 

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/367415

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

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

 

This is how it should look like:

Ziel_ProdStatistik.png

amitchandak
Super User
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 ,

 

thanks for your reply.

 

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

 

This is how it should look like:

 

Ziel_ProdStatistik.png

 

the data set is already limited to 2019.

 

Do you have an idea how to get there?

 

Thanks for you support!

freiburgc

@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.

 

 

@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]))

 

 

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/367415

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.