Display Slices in Pie chart based on parameter values (Top N + Others)

[Venkat124]

Hi All,

I am hoping someone can help me out with this. I feel like I am close but just not getting the results I need. 

I have Pie chart which displays sales state wise, I need to display legend values (slices) based on the parameter number with respect to sales values rank wise.

Scenario 1: 

If I select parameter value as 5, the pie chart should display 6 Slices,

5 Slices - States with Top 5 sales

6th Slice -Should club remaining States and display as  "Others" with Sum of Sales for remaining States

Here is the screenshot below attached, kindly help me.

[v-jianboli-msft · Accepted solution]

Hi @Venkat124 ,

Based on your description, I have created a simple sample:

Please try:

First create a new table for slicer:

For Slicer = 
var _a = SUMMARIZE('Table',[State],"Rank",RANKX(ALL('Table'),CALCULATE(SUM('Table'[Value]),ALLEXCEPT('Table','Table'[State]))))
var _b = UNION(_a,{("Others",0)})
return _b

Output:

Create a parameter for slicer:

Then Create a measure and apply it to the Pie chart's visual level filter:

Measure = 
var _a = IF(MAX('For Slicer'[Rank])<=SELECTEDVALUE(Parameter[Parameter]),1)
return SWITCH(TRUE(),
ISFILTERED(Parameter[Parameter]),_a,
NOT(ISFILTERED(Parameter[Parameter]))&&MAX('For Slicer'[State])<>"Others",1)

Then create a measrue and apply it to the value:

SumValue =
VAR _a =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER ( 'Table', [State] = MAX ( 'For Slicer'[State] ) )
    )
VAR _b =
    SELECTCOLUMNS (
        FILTER ( ALL ( 'For Slicer' ), [Rank] > SELECTEDVALUE ( Parameter[Parameter] ) ),
        "Others", [State]
    )
VAR _c =
    CALCULATE ( SUM ( 'Table'[Value] ), FILTER ( 'Table', [State] IN _b ) )
RETURN
    IF ( MAX ( 'For Slicer'[State] ) = "Others", _c, _a )

Final output:

Best Regards,

Jianbo Li

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

[Venkat124]

Thank you so much for help Jianbo Li,

But I have different dimensions also apart from State, like as I mentioned in the screenshot- dynamic slicer (Category, city, country/Region, customer name, product name) coming from different dimension tables

So do I need to create multiple tables. If I create single table for each dimension (Var _a), then how can I consider the ranking

Please help me for this scenario with dynamic slicer (coming from multiple dimension tables)

Once again many thanks.

Thanks & Regards

Venkat T