Redundand values

[Tatiana-Weiss]

Hello community,

How do I aggregate all the values that are not top 10 of sum values as 'other'?

Many thanks,

[v-pnaroju-msft]

Hi Tatiana-Weiss,

We have not received a response from you regarding the query and were following up to check if you have found a resolution. If you have identified a solution, we kindly request you to share it with the community, as it may be helpful to others facing a similar issue.

If you find the response helpful, please mark it as the accepted solution, as this will help other members with similar queries.

Thank you.

[v-pnaroju-msft]

Thankyou, @burakkaragoz, @FarhanJeelani and @Elena_Kalina for your responses.

Hi @Tatiana-Weiss,

We sincerely appreciate your inquiry submitted through the Microsoft Fabric Community Forum.

A new summary table has been created to pre-aggregate the total sales for each product. A ranking has been applied to identify the top 10 products based on their total sales values. Additionally, a grouping column has been introduced to label each product either by its name (if it ranks within the Top 10) or as "Other" (if it falls outside the Top 10).

This summary table is utilized in the Power BI table visual, where the product group and total sales fields are displayed. Consequently, the Top 10 products appear individually, while all remaining products are combined under a single row labelled "Other".

Please find attached a screenshot and the PBIX file, which we hope will assist in resolving the issue:

If you find our response helpful, we kindly request you to mark it as the accepted solution and provide kudos. This will assist other community members who may face similar queries.

Should you have any further questions, please feel free to reach out to the Microsoft Fabric Community.

Thank you.

[Elena_Kalina · Accepted solution]

Hi @Tatiana-Weiss 

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])

    )

RETURN

    IF(

        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.

[Tatiana-Weiss]

This actually works.

Thank you so much, @Elena_Kalina 

[FarhanJeelani]

Hi @Tatiana-Weiss ,

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.

[Tatiana-Weiss]

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.

[burakkaragoz]

Hi @Tatiana-Weiss ,

You can do this by creating a calculated column or measure that checks if each value is in the top 10, and if not, labels it as 'Other'. In Power BI, you could use RANKX to rank your values, then use an IF statement like:
IF([Rank] <= 10, [YourCategory], "Other")
This way, when you use it in your visuals, everything not in the top 10 will be grouped under 'Other'.

Let me know if you want some DAX code to get started!

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.