Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Let's say I have three columns:
NAME: Name of personel
SALES: Amount to be summed
DATE: date
I can not manage to make a top 10 of NAMEs that have the highest SALES per DATE
For example if this is data:
Name | Date | Sales |
A | 1 | 60 |
A | 1 | 150 |
B | 3 | 225 |
C | 2 | 150 |
D | 5 | 125 |
A | 2 | 100 |
A top 3 should look like this:
Name | Date | Sales |
B | 3 | 225 |
A | 1 | 210 |
C | 2 | 150 |
I can make a table sorted by Sales of above three columns showing all data with correct sum of sales per date per name, however once I filter that visual by Name -> Top N - > top 10 by Sales, I get complete gibberish. How do I go about this?
Solved! Go to Solution.
@Anonymous , Try a new measure like this and filter for <=3
rankx(allselected(Table[name], table[date]), calculate(sum(table[sales])))
For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
ttps://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/367415
OK so this is weird. Your solution DOES work, but not yet in the way I want it to.
The top 10 is made from data from table A.
Table A contains a date column, which contains weirdly formatted dates that I don''t use anywhere in the report.
Table A is linked to column B through a Transaction ID (each transaction ID = one row that also contains name and sales amount). Column B has the correct date format for each transaction ID. The date format in column B is linked to a Date Table (dates, weeks, months, years) that I use throughout the report and seems to work correctly when summarizing data from Table A.
However... when I use the date column from the Date Table in my Top N table, I get gibberish values. If I use the date column from Table A, I get a correct top 10 table. Even though the date translation (date, weeks, months, etc.) seems to work correctly for all other visualizations that use data from Table A?
How do I make the table with the correct dates from the Date Table?
EDIT: I'll mark this as solved as a solution was given for the original question. I'll post the new question in a new topic.
@Anonymous , Can you share a bigger sample. I tried the measure I shared above and got the results
@Anonymous , Try a new measure like this and filter for <=3
rankx(allselected(Table[name], table[date]), calculate(sum(table[sales])))
For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
ttps://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/367415
Thank you for the quick reply. I've seen this posted and tried it, but this doesn't give the desired result.
If I just make the table (no top N, no fancy filtering), the top 10 of sales per date per employee (in a very very large table) show the correct numbers. There are no duplicates of values in the top 10 of that very large table. However once I try your solution to make a actual top 10 list, the highest value is not on top anymore. There is a whole bunch of values that have ranking "1", i.e. I end up with a much larger list than a Top 10, even though there are no duplicate values in the actual top 10 ?
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
99 | |
85 | |
35 | |
35 |
User | Count |
---|---|
152 | |
107 | |
78 | |
60 | |
56 |