cancel
Showing results for 
Search instead for 
Did you mean: 
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



Learn Power BI - Full Course with Dec-2022, with new DAX functions like Window, Index, Offset !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Formatted Profit and Loss Statement with empty lines

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)



Learn Power BI - Full Course with Dec-2022, with new DAX functions like Window, Index, Offset !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Formatted Profit and Loss Statement with empty lines
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



Learn Power BI - Full Course with Dec-2022, with new DAX functions like Window, Index, Offset !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Formatted Profit and Loss Statement with empty lines
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



Learn Power BI - Full Course with Dec-2022, with new DAX functions like Window, Index, Offset !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Formatted Profit and Loss Statement with empty lines

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.