cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jtan20
Frequent Visitor

100% Stacked Bar Chart with what-if parameter

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

 

I have this table

IDAmount
112
140
110
120
230
240
250
360
370

 

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?

1 ACCEPTED SOLUTION
v-kalyj-msft
Community Support
Community Support

Hi @jtan20 ,

According to your description, here's my solution.

1.Create a section table containing the four sections.

vkalyjmsft_0-1650615851107.png

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.

vkalyjmsft_1-1650615968412.png

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.

View solution in original post

1 REPLY 1
v-kalyj-msft
Community Support
Community Support

Hi @jtan20 ,

According to your description, here's my solution.

1.Create a section table containing the four sections.

vkalyjmsft_0-1650615851107.png

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.

vkalyjmsft_1-1650615968412.png

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.

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors