Add 'Other' option to stacked column Graph

[CM_Mills]

Hi,

I’m having issues where on a stacked column graph I have too many items in the legend.

To solve this I would like to convert any item with under 5% of the total as ‘Other’

The legend name in a different table to the values so they will need to link some how in the solution. The value is also a ‘measure’ that has been created.

Table 1

                ItemLabel

Table 2

                ItemValue (Measure)

Thank you for any help

[Anonymous · Accepted solution]

Hi @CM_Mills ,

I created an example using this data:

Create a calculated column and write down as other if the product is less than 15% of the current country classification.

Column = 
VAR _sum1 =
    CALCULATE (
        SUM ( financials[ Sales] ),
        FILTER (
            ALL ( financials ),
            'financials'[Country] = EARLIER ( 'financials'[Country] )
        )
    )
VAR _sum2 =
    CALCULATE (
        SUM ( financials[ Sales] ),
        FILTER (
            ALL ( financials ),
            'financials'[Country] = EARLIER ( 'financials'[Country] )
                && 'financials'[Product] = EARLIER ( 'financials'[Product] )
        )
    )
RETURN
    IF ( _sum2 >= 0.15 * _sum1, 'financials'[Product], "Other" )

If the results don't match your expectations. Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

Best Regards,

Clara Gong

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

[Kaviraj11]

Showing the top 5 products and Other row - SQLBI

[CM_Mills]

Hi, thank you for reviewing but this helps with the other solution but does this by using a set top number of items. I need this work on percentages.

[lbendlin]

Filtering the top products alongside the other products in Power BI - SQLBI

[CM_Mills]

Hi, thank you for reviewing but this helps with the other solution but does this by using a set top number of items. I need this work on percentages.

[lbendlin]

You will not be able to get an exact match with percentages.  You would have to define what "under 5%"  means in your scenario, and there may be no data points satisfying that criteria.