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'm trying to create a topN + other values table. As a simple example, i have a table that looks like this:
customer | amount |
A | 1 |
B | 2 |
C | 3 |
D | 4 |
E | 5 |
and i would like to have a table that produces the following result, for a selected value of TopN (for example N = 3)
customer | amount |
E | 5 |
D | 4 |
C | 3 |
Others | 3 |
Note that "Others" represent the sum of everything else that isn't in top 3
I am able to get the Top 3 table, as well as the value of Others through a new measure separately with the formula
Solved! Go to Solution.
Hi @Anonymous
To calculate Others you will need to create a Column with the expression like below.
Rank Top 3 Products and Others = -- add this column to your customer table VAR rnk = RANKX( 'Product', -- replace with customer table CALCULATE(SUM(Sales[Quantity])),,, -- amount column Dense ) <= 3 RETURN IF(rnk, FORMAT('Product'[Product Code], ""), "Others") -- replace Product Code with Customer
Regards,
Mariusz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous
To calculate Others you will need to create a Column with the expression like below.
Rank Top 3 Products and Others = -- add this column to your customer table VAR rnk = RANKX( 'Product', -- replace with customer table CALCULATE(SUM(Sales[Quantity])),,, -- amount column Dense ) <= 3 RETURN IF(rnk, FORMAT('Product'[Product Code], ""), "Others") -- replace Product Code with Customer
Regards,
Mariusz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you for your help! The code worked perfectly, but i just have some questions about the code itself for better understanding,
In the "Rank Top 3 Products and Others" code, what is the purpose of the FORMAT code?
Hi @Anonymous
FORMAT(123 ,"") will return 123 as text.
IF without third argument will produce BLACK , when added to a visual it will produce only top 3.
Hope this helps
Mariusz
HI @Anonymous,
You can use the expresion below.
Rank Top Three Sales = VAR rnk = RANKX( CALCULATETABLE( VALUES('Product'[Brand]), --Replace with your customer ALLSELECTED() ), [Sales],, --Replace with your amount DESC ) <= 3 RETURN IF(rnk, [Sales]) --Replace [Sales] with your amount
Regards,
Mariusz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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 |
---|---|
112 | |
99 | |
75 | |
64 | |
63 |
User | Count |
---|---|
140 | |
105 | |
102 | |
81 | |
67 |