Forum Discussion
Redundand values
Hi Anonymous
Please try this one:
Product Grouping =VAR CurrentCategory = SELECTEDVALUE('Superstore'[Product Name])VAR Top10Categories =CALCULATETABLE(TOPN(10,VALUES('Superstore'[Product Name]),[Total_Sales],DESC),ALLSELECTED('Superstore'[Product Name]))RETURNIF(CurrentCategory IN Top10Categories,[Total_Sales],"Other")If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!
Thank you.
Hi Anonymous ,
To aggregate all values outside the Top 10 (based on a sum measure) as "Other" in Power BI, you can follow these steps:
Scenario:
You have a table like:
| Category | Value |
| A | 500 |
| B | 400 |
| C | 300 |
| ...... | ..... |
| Z | 1000 |
You want a visual to show:
Top 10 categories (by total value),
Everything else grouped into “Other”.
Solution Using DAX (Calculated Table or Measure):
Step 1: Create a Rank Measure
dax
Category Rank =
RANKX(
ALL('YourTable'[Category]),
CALCULATE(SUM('YourTable'[Value])),
,
DESC,
DENSE
)
Step 2: Create a Grouping Column (Calculated Column)
Category Group =
IF(
[Category Rank] <= 10,
'YourTable'[Category],
"Other"
)Use this column in your visual instead of the raw Category.
Step 3: Use the Measure in Your Visual
Drag:
Category Group as Axis/Rows,
The original SUM(Value) as your value.
Now your chart will show:
Top 10 categories individually,
Remaining as one grouped row: "Other".
Please mark this post as solution if it helps you. Appreciate Kudos.
Hello Fahran,
Thank you for your help. This seems as the best solution, (I proceeded similarly) but for whatever reason, the visual is still showing all the categories. Do you have an idea what to do about it?
T.