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
goyasor's avatar
goyasor
Frequent Visitor
9 years ago

Thanks for your response. Unfortunately that didn't work for me.

 

I'm getting a constant figure in the Periods column:

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