Forum Discussion
Waterfall variation by sales type
Hi, I'm trying to create a waterfall that shows: First Bar: 2015 total Middle Bars: Year over Year Change End Bar: 2016 Total For example: First Bar: 2015 total = 20M Cars Middle B...
Here's the raw data I used in Excel:
| Manufactuter | Category | 2015 Sales | 2016 Sales | Variance 2015-16 |
| GM | Country A | 1,000,000 | 2,000,000 | 1,000,000 |
| GM | Country B | 1,000,000 | 2,400,000 | 1,400,000 |
| Ford | Country A | 3,000,000 | 1,900,000 | -1,100,000 |
| Ford | Country B | 4,000,000 | 3,000,000 | -1,000,000 |
| Toyota | Country A | 1,000,000 | 2,000,000 | 1,000,000 |
| Toyota | Country B | 3,000,000 | 3,800,000 | 800,000 |
| Honda | Country A | 2,000,000 | 1,000,000 | -1,000,000 |
| Honda | Country B | 2,500,000 | 2,000,000 | -500,000 |
| Chrysler | Country A | 500,000 | 900,000 | 400,000 |
| Chrysler | Country B | 1,000,000 | 2,000,000 | 1,000,000 |
| TOTAL | 19,000,000 | 21,000,000 |
I used it to create this summary table (also in Excel):
| 2015 Sales | 19,000,000 |
| GM | 2,400,000 |
| Ford | -2,100,000 |
| Toyota | 1,800,000 |
| Honda | -1,500,000 |
| Chrysler | 1,400,000 |
| 2016 Sales | 21,000,000 |
Then finally the Waterfall chart:
Vvelarde
Community Champion
Community Champion
Hi, One Way to obtain this :
Create a New Table (Modeling Menu)
Table =
UNION (
SUMMARIZECOLUMNS (
Table1[Manufacturer],
"Variation", SUM ( Table1[2016 Sales] ) - SUM ( Table1[2015 Sales] )
),
ROW ( "Manufacturer,; "2015 Sales", "Variation", SUM ( Table1[2015 Sales] ) )
)After That, Use the Waterfall Chart with the fields of the new table
Hello Vvelarde could you share the pbix template? im trying to simulate what you did because this solution will help me to extrapolate it to another problem that I have, but I'm getting errors in the formula.
Table =
UNION (
SUMMARIZECOLUMNS (
Table1[Manufacturer],
"Variation", SUM ( Table1[2016 Sales] ) - SUM ( Table1[2015 Sales] )
),
ROW ( "Manufacturer,; "2015 Sales", "Variation", SUM ( Table1[2015 Sales] ) )
)