topic Re: Add 'Other' option to stacked column Graph in DAX Commands and Tips

Add 'Other' option to stacked column Graph

CM_Mills — Thu, 03 Oct 2024 15:58:24 GMT

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

Re: Add 'Other' option to stacked column Graph

lbendlin — Thu, 03 Oct 2024 17:09:57 GMT

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

Re: Add 'Other' option to stacked column Graph

Kaviraj11 — Thu, 03 Oct 2024 17:20:55 GMT

Showing the top 5 products and Other row - SQLBI

Re: Add 'Other' option to stacked column Graph

CM_Mills — Fri, 04 Oct 2024 07:29:02 GMT

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.

Re: Add 'Other' option to stacked column Graph

CM_Mills — Fri, 04 Oct 2024 07:29:11 GMT

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.

Re: Add 'Other' option to stacked column Graph

Anonymous — Fri, 04 Oct 2024 08:35:04 GMT

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.

Re: Add 'Other' option to stacked column Graph

lbendlin — Fri, 04 Oct 2024 12:19:27 GMT

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.