Club or add small pie slices to others IN Pie chart with Date and Customer ID filters applied

[Anil_1502]

Dear All,

              We are trying to club or add  smaller slices whose percentage of total value is less than 1%  to Others which is already present in the Category column. Also Customer ID and Month Year fileters are applied. Tried using summary table for Category column and created measure to add category values if % is < 1 to others, but as there is already Others precent in the Category column, unable to add the lesser values to Others.

Sample .pbix :

https://drive.google.com/file/d/10Eq7UhhuCyxrBWBlnqQoJNkGnIQkI9X1/view

PLease help me to solve the mentioned senario.

Thanks and Regards.

[TomMartens · Accepted solution]

Hey @Anil_1502 ,

this measure does what you are looking for:

Measure = 
var currentCategory = SELECTEDVALUE( 'Sheet1'[Category] )
var totalSales = 
    SUMX(
        CALCULATETABLE(
             VALUES('Sheet1'[Category] )
             , ALL( 'Sheet1'[Category] )
        )
        , CALCULATE( SUM( 'Sheet1'[Sales] ) )
    )
var OtherSales = CALCULATE( SUM( 'Sheet1'[Sales] ) , 'Sheet1'[Category] = "Others" )
Var OtherSalesNew =
    OtherSales +
    SUMX(
        CALCULATETABLE(
             VALUES('Sheet1'[Category] )
             , ALL( 'Sheet1'[Category] )
        )
        , IF( 'Sheet1'[Category] <> "Others"  
            , if(divide( CALCULATE( SUM( 'Sheet1'[Sales] ) ) , totalSales) < 0.01 
                , CALCULATE( SUM( 'Sheet1'[Sales] ) )
                , BLANK()
            )
            , BLANK()
        )
    )
return
   IF(
        currentCategory = "Others"
        , OtherSalesNew
        , if(
            divide( CALCULATE( SUM( 'Sheet1'[Sales] ) ) , totalSales) < 0.01
            , BLANK()
            , CALCULATE( SUM( Sheet1[Sales] ) )
        )
   )
        

The table and the donut:

The configuration of the donut:

Hopefully, this provides what you are looking for.

Regards,

Tom

[Anil_1502]

@TomMartens Thank you so much for the reply. If possible please share the sample. pbix file.

[TomMartens]

Hey @Anil_1502 ,

I attached the pbix to my post containing the DAX statement.

Regards,

Tom

[Anil_1502]

Thank you @TomMartens 

[TomMartens]

Hey @Anil_1502 

I recommend following this article: https://www.sqlbi.com/articles/showing-the-top-5-products-and-others-row/

If you consider the sqlbi approach too complex try this one: https://www.minceddata.info/2018/06/06/topsomething-and-all-the-rest-called-other/

Hopefully, this helps to tackle your challenge.

Regards,

Tom

[Anil_1502]

@TomMartens Thanks for the reply. The requirement is to add smaller values less than 1% to the already existing Others field of Category column.

[Anil_1502]

Dear all, need help in achieveing the mentioned scenario.