Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Redundand values

Hello community, How do I aggregate all the values that are not top 10 of sum values as 'other'? Many thanks,
Need Help
  • Elena_Kalina's avatar
    Elena_Kalina
    1 year ago

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

     

v-pnaroju-msft's avatar
v-pnaroju-msft
Community Support
1 year ago

Thankyou, burakkaragoz, FarhanJeelani and Elena_Kalina for your responses.

Hi Anonymous,

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.