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.
Hello,
I have a report where view sales volume by supplier. In my visualisation i want to only show the top 10 suppliers.
The problem is that sales volume is a measure, created by adding two other measures together.
These measures originate from 4 different columns that come from two different tables.
Because this is really complicated to explain I've tried to visualize like this:
Black names are columns and red names are measures.
I've tried working with TOPN and with RANKX but I cant rank it on one specific column as the volume is relying on the value of more than one column!
Does anyone have any ideas on how to solve this?
/ Johannes
Solved! Go to Solution.
Well i managed to solve part of the issue. A normal Rankx(ALL(Table[Column]);[Measure]) managed to give proper ranking values to all measurement values. Now the issue is that when i apply external filters the ALL() method results in the ranking ignore the current filter.
This results in the remaining objects having ranks something like; 1,4,6,7,9 and therefore when i put a filter to only display rank<11 i get lesss than 10 results.
I can sadly not share the tables due to company policy. I will try to recreate the situation with dummy data.
/ Johannes
Hi tex628,
Are you trying to rank values by 2 category columns, right? If my understanding is right, you can refer to DAX formula pattern of below:
Rank by Categories: Rank = RANKX(FILTER(Table, Table[Category] = EARLIER(Table[Category])), RANKX(ALL(Table), Table1[Value]), , ASC, Dense)
If not so, could you clarify more details about your requirement, if possible, can you post your tables for further analysis?
Regards,
Jimmy Tao
Well i managed to solve part of the issue. A normal Rankx(ALL(Table[Column]);[Measure]) managed to give proper ranking values to all measurement values. Now the issue is that when i apply external filters the ALL() method results in the ranking ignore the current filter.
This results in the remaining objects having ranks something like; 1,4,6,7,9 and therefore when i put a filter to only display rank<11 i get lesss than 10 results.
I can sadly not share the tables due to company policy. I will try to recreate the situation with dummy data.
/ Johannes
Well i managed to solve part of the issue. A normal Rankx(ALL(Table[Column]);[Measure]) managed to give proper ranking values to all measurement values. Now the issue is that when i apply external filters the ALL() method results in the ranking ignore the current filter.
This results in the remaining objects having ranks something like; 1,4,6,7,9 and therefore when i put a filter to only display rank<11 i get lesss than 10 results.
I can sadly not share the tables due to company policy. I will try to recreate the situation with dummy data.
/ Johannes
Hi tex628,
"Now the issue is that when i apply external filters the ALL() method results in the ranking ignore the current filter. "
<--- Could you please provide more details about your issue?
Regards,
Jimmy Tao
You should be able to reference your calculated measure in a rankx without any issues I think:
Calculate(rankx(ALL('Table'[Supplier]),[Total Sales Volume]))
Hi @tex628
As I understand, you want to show the top 10 suppliers and the top 10 are filtered by the Total sales volume, is this rigth? If so then you can filter your data after making the visualisation itself.
If possible could you please provide a sample data having the given columns and measures as you have in your tables.
Regards,
Rohit
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 |
---|---|
107 | |
97 | |
75 | |
65 | |
53 |
User | Count |
---|---|
144 | |
103 | |
98 | |
85 | |
64 |