Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.