Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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

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.

 

Need Help
  • v-jianboli-msft's avatar
    v-jianboli-msft
    3 years ago

    Hi Anonymous ,

     

    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.

2 Replies

  • v-jianboli-msft's avatar
    v-jianboli-msft
    Community Support
    3 years ago

    Hi Anonymous ,

     

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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