Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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?

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    Hi Anonymous ,

    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.

1 Reply

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi Anonymous ,

    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.