Forum Discussion

CM_Mills's avatar
CM_Mills
Icon for Helper II rankHelper II
2 years ago
Solved

Add 'Other' option to stacked column Graph

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's avatar
    Anonymous
    2 years ago

    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.

6 Replies

    • CM_Mills's avatar
      CM_Mills
      Icon for Helper II rankHelper II
      2 years ago

      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's avatar
        lbendlin
        Icon for Super User rankSuper User
        2 years ago

        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.

    • CM_Mills's avatar
      CM_Mills
      Icon for Helper II rankHelper II
      2 years ago

      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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.