100% Stacked Bar Chart with what-if parameter

[jtan20]

I have created a whatif parameter and added it as a slicer on a page. 

I have this table

ID	Amount
1	12
1	40
1	10
1	20
2	30
2	40
2	50
3	60
3	70

I want to sum these values by ID and create a stacked bar chart which is divided into 4 sections based on the value of the silcer paramter,

- below 50%

- between 50 to 80%

- between 80 to 100%

- over 100%

I want to be able to see how many ID's are in each of these 4 categories in a stacked bar chart.

For example if the silcer value is set to 50

- below 50% - should include ID's with sum of Amount less 25

- over 100% - should include ID's with sum of Amount  over 50

If the silcer value is set to 100

- below 50% - should include ID's with sum of Amount less 50

- over 100% - should include ID's with sum of Amount  over 100

How would I go about this? Would I uses a switch case?

[v-kalyj-msft · Accepted solution]

Hi @jtan20 ,

According to your description, here's my solution.

1.Create a section table containing the four sections.

2.Create a measure.

Measure =
VAR _P =
    SELECTEDVALUE ( Parameter[Parameter] )
RETURN
    SWITCH (
        MAX ( 'Section Table'[Section] ),
        "below 50%",
            CALCULATE (
                DISTINCTCOUNT ( 'Table'[ID] ),
                FILTER (
                    ALL ( 'Table' ),
                    SUMX (
                        FILTER ( ALL ( 'Table' ), 'Table'[ID] = EARLIER ( 'Table'[ID] ) ),
                        'Table'[Amount]
                    ) < 0.5 * _P
                )
            ),
        "between 50 to 80%",
            CALCULATE (
                DISTINCTCOUNT ( 'Table'[ID] ),
                FILTER (
                    ALL ( 'Table' ),
                    SUMX (
                        FILTER ( ALL ( 'Table' ), 'Table'[ID] = EARLIER ( 'Table'[ID] ) ),
                        'Table'[Amount]
                    ) >= 0.5 * _P
                        && SUMX (
                            FILTER ( ALL ( 'Table' ), 'Table'[ID] = EARLIER ( 'Table'[ID] ) ),
                            'Table'[Amount]
                        ) < 0.8 * _P
                )
            ),
        "between 80 to 100%",
            CALCULATE (
                DISTINCTCOUNT ( 'Table'[ID] ),
                FILTER (
                    ALL ( 'Table' ),
                    SUMX (
                        FILTER ( ALL ( 'Table' ), 'Table'[ID] = EARLIER ( 'Table'[ID] ) ),
                        'Table'[Amount]
                    ) >= 0.8 * _P
                        && SUMX (
                            FILTER ( ALL ( 'Table' ), 'Table'[ID] = EARLIER ( 'Table'[ID] ) ),
                            'Table'[Amount]
                        ) <= _P
                )
            ),
        "over 100%",
            CALCULATE (
                DISTINCTCOUNT ( 'Table'[ID] ),
                FILTER (
                    ALL ( 'Table' ),
                    SUMX (
                        FILTER ( ALL ( 'Table' ), 'Table'[ID] = EARLIER ( 'Table'[ID] ) ),
                        'Table'[Amount]
                    ) > _P
                )
            )
    )

3.In the 100% Stacked Bar Chart, put the Section column in the Legend, the measure in the Values, get the correct result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

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