Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
I just saw that my target table wasn´t uploaded.
This is how it should look like:
@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)
Hi @amitchandak ,
thanks for your reply.
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
@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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
80 | |
67 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |