Forum Discussion
Waterfall chart between two measures by dimension
Hello,
I am trying to create a waterfall chart that shows the gap between two calculated measures by a dimension. However waterfall should show the only top "offenders" in terms of gap between the two measure. Much like the photo below, in the example below the top two customers are shown and the rest are aggregated under Other. I have done this before in other BI tools however I cant seem to find a straight forward way to do it in PBI.
Expected Output:
I have the dataset and example calculation here
Measure Formulas:
| MeasureA -> | P+L |
| MeasureB -> | P+L+C |
Measure Calculation:
| MeasureA | |
| CustomerA | 350 |
| CustomerB | 450 |
| CustomerC | 400 |
| CustomerD | 450 |
| CustomerE | 250 |
| 1900 |
| MeasureB | |
| CustomerA | 200 |
| CustomerB | 350 |
| CustomerC | 200 |
| CustomerD | 350 |
| CustomerE | 150 |
| 1250 |
Dataset:
| Year | Customer | Block | Value |
| 2023 | CustomerA | P | 100 |
| 2023 | CustomerA | L | 250 |
| 2023 | CustomerA | C | -150 |
| 2023 | CustomerB | P | 200 |
| 2023 | CustomerB | L | 250 |
| 2023 | CustomerB | C | -100 |
| 2023 | CustomerC | P | 150 |
| 2023 | CustomerC | L | 250 |
| 2023 | CustomerC | C | -200 |
| 2023 | CustomerD | P | 150 |
| 2023 | CustomerD | L | 300 |
| 2023 | CustomerD | C | -100 |
| 2023 | CustomerE | P | 100 |
| 2023 | CustomerE | L | 150 |
| 2023 | CustomerE | C | -100 |
I am familiar with the general method that involves creating a disconnected table + using a switch measure to get the gap however you have to hard code the dimension values in the disconnected table.
Any help? Thank you!
1 Reply
- DataInsights
Super User
Anonymous,
Here's an article that explains how to achieve this:
https://www.sqlbi.com/articles/filtering-the-top-products-alongside-the-other-products-in-power-bi/