Select a column to show in chart based on slicer having only one selected value

[Alex_pg]

Hi,

I have been trying to create a donut chart that would automatically drill down based on a slicer selection having only one value selected.

Dataset is simple, [Major category] [Minor category] [Total].

Major category | Minor category | Total
A                        | 1                       | 20

A                        | 2                       | 55

A                        | 3                       | 36

B                        | 1                       | 8
B                        | 2                       | 3

In the report there is a [Major category] slicer.

There also is a donut chart that displays the [Total] based on [Major category].

I would like to find a way to automatically change the detail of the donut to [Minor category] if/when only one [Major category] is selected in the slicer.

I am not sure how to do this (I am open to suggestion - Columns / measure / table).

I tried a if condition in a table but it did not work.

_TBL_DONUT_CHART =

IF(

    DISTINCTCOUNT([Major category])=1

    ,SUMMARIZECOLUMNS(

       [Minor category]

        ,"sum_Total"

        ,SUM([Total])

    )

    ,SUMMARIZECOLUMNS(

        [Major category]

        ,"sum_Total"

        ,SUM([Total])

    )

)

Hopefully this make sense.

Regards,

Alex

[parry2k]

@Alex_pg There is no method to auto drill-down to the next level based on the selection in the slicer. 

[CoreyP · Accepted solution]

Why not just use the built in drill-down feature of the pie chart? So when someone clicks a slice of Major pie, it expands to all the Minor slices of that Major slice?

[Alex_pg]

I just wanted to limit the number of clicks since there is already a filter in the report.
I will just use the drill-down I guess.

Thank you all