Forum Discussion

goyasor's avatar
goyasor
Frequent Visitor
9 years ago

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...
Need Help
Tips and Tricks
MFelix's avatar
MFelix
Icon for Super User rankSuper User
9 years ago

Be aware that this is a measure an not a calculated column that I use to show the percentages.

 

If you can share some data I can try to simulate and redefine the formula.

 

MFelix

goyasor's avatar
goyasor
Frequent Visitor
9 years ago

Here's the raw data I used in Excel:

 

ManufactuterCategory2015 Sales2016 SalesVariance 2015-16
GMCountry A  1,000,0002,000,0001,000,000
GMCountry B1,000,0002,400,0001,400,000
FordCountry A3,000,0001,900,000-1,100,000
FordCountry B4,000,0003,000,000-1,000,000
ToyotaCountry A1,000,0002,000,0001,000,000
ToyotaCountry B3,000,0003,800,000800,000
HondaCountry A2,000,0001,000,000-1,000,000
HondaCountry B2,500,0002,000,000-500,000
ChryslerCountry A500,000900,000400,000
ChryslerCountry B1,000,0002,000,0001,000,000
TOTAL 19,000,00021,000,000 

 

 

I used it to create this summary table (also in Excel):

2015 Sales19,000,000
GM2,400,000
Ford-2,100,000
Toyota1,800,000
Honda-1,500,000
Chrysler1,400,000
2016 Sales21,000,000

 

 

Then finally the Waterfall chart:

  • Vvelarde's avatar
    Vvelarde
    Icon for Community Champion rankCommunity Champion
    9 years ago

    goyasor

     

    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

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      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] ) )
      )