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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors