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.
Dear all, I have the following table:
TABLE | |
Supplier | Net Value |
A | 3000 |
B | 1500 |
C | 2000 |
A | 1000 |
D | 2000 |
E | 4000 |
B | 1000 |
E | 500 |
F | 850 |
If I create a Table with a Top3 Rank + Others, it will look like this:
Supplier | TOTAL |
E | 4500 |
A | 4000 |
B | 2500 |
Others | 4850 |
But I just want to create a Calculated Colum (DAX) to show like this:
TABLE | ||
Supplier | Net Value | Supplier_Top3 |
A | 3000 | A |
B | 1500 | B |
C | 2000 | Others |
A | 1000 | A |
D | 2000 | Others |
E | 4000 | E |
B | 1000 | B |
E | 500 | E |
F | 850 | Others |
I found some solutions but just when the Supplier appears once. In this case, the same supplier can appear many times in the database.
Thanks in advance,
Joao
Solved! Go to Solution.
Hi,
Write these calculated column formulas
Value of supplier = CALCULATE(SUM(Data[Net Value]),FILTER(Data,Data[Supplier]=EARLIER(Data[Supplier])))
Rank = if(RANKX(ALL(Data),Data[Value of supplier],,,dense)<=3,Data[Supplier],"Others")
Hope this helps.
Hi,
Write these calculated column formulas
Value of supplier = CALCULATE(SUM(Data[Net Value]),FILTER(Data,Data[Supplier]=EARLIER(Data[Supplier])))
Rank = if(RANKX(ALL(Data),Data[Value of supplier],,,dense)<=3,Data[Supplier],"Others")
Hope this helps.
I know there is a more efficient way to write this expression, but it works for what you are looking for I believe. FYI that in your sample data, Supplier B was marked both as B and Others. If that was correct, I misunderstood your request. This expression returns B for all B rows.
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Hi, thank you for the quick response, I edited the post regarding "Supplier B".
About the proposal, I do not have this table "[TotalNetValue]" so I couldn't solve this issue. We just have one table with the two columns "Suppliers" and "Net Value".
Regards,
Joao
Sorry I didn't clarify that. The TotalNetValue is just a measure that is sum of the Net Value column.
Regards
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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 |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |