7 Replies Latest reply on Jun 16, 2018 12:12 PM by Steve Martin

    Waterfall with multiple Measures

    Kiran K

      Hello All,

       

      Trying to achieve Steve's trick to get waterfall chart using Tableau Public .

      In attached workbook (10.3.1 v), Pillar values are calculated by subtracting cost elements in database. Say Pillar 1 value calculated as Pillar 0 - Cost Element 1 and so on. When used Running Sum on Tableau, it's adding up each measure values, showing as below

       

      But I want always pillars should start from X-Axis and cost elements should hang up/down based on value (negative or positive) as below.

       

       

      Steve Martin, your help very much appreciated.

       

      Thanks !

        • 1. Re: Waterfall with multiple Measures
          Jeevan Krishna

          Hi Kiran,

           

          May I know what might be the reason why you are sticking to Measures based waterfall chart. From the link mentioned, there seems to be multiple caveats for this process.

           

          You can pivot all your measures which you are trying to plot and do the usual waterfall, which is consistent and doesn't bring in extra ruse.

          • 2. Re: Waterfall with multiple Measures
            Kiran K

            Hi,

             

            Have developed the chart by traditional way with pivot data. Now, have data of 120 million records, pivoting these many records (120 * 30 measures), will be a crazy extract and refresh of extract will take very long time. To avoid this, wan to build again. Welcome if have any other thoughts to bring waterfall.

             

            Thanks !

            Kiran

            • 3. Re: Waterfall with multiple Measures
              Steve Martin

              Hi Kiran,

               

              I can look into this later when I reach home for you.

               

              Jeevan Krishna, I am not sure whether you downloaded the workbook but there is actually just one caveat required to operate. You see, this waterfall is different from a normal waterfall in that a normal waterfall (exploded stacked bar-chart if you will) plots a single (or multiple measures (multiple if a side-by-side bar-chart) against many dimensions; my measures waterfall is designed to plot each measure as a pillar in order to see how the multiple elements leads to the final value: so instead of seeing how each sub-product sale provides a bottom-line, it is possible say from a manufacturing process to understand the cost-of-goods value by exploding the individual cost elements of materials, operating costs, taxes, salaries etc.

              But, Tableau cannot natively plot measures in a single-chart to sum to a total in this way such, that it requires a more programmatic approach, to be told what to plot where, the upshot being that you do have ultimate control on the final layout.

               

              Kiran K, It is this engineering approach that whilst not an issue per se, will generate a lot of additional work for you as you will have to manually build the elemental process for each of your 32 components.

               

              Steve

              • 4. Re: Waterfall with multiple Measures
                Kiran K

                Thanks Steve ! Please use updated sheet .

                 

                Thanks !

                Kiran

                • 5. Re: Waterfall with multiple Measures
                  Steve Martin

                  Hi Kiran,

                   

                  Can I ask, why are you plotting the blue bars? Being sub-totals, they actually confuse the chart unless you annotate them differently, maybe even wash them out a little to reduce their presence.

                  Understanding their purpose could mean the difference between the highly engineered process of a measures waterfall and against a reversed waterfall working right-to-left.

                   

                  But in order to achieve your requested output, as you are in control of the plot, you can determine which fields need to be plotted as positive to negative. This is achieved by simply placing a dash (negative) before each value element of the [02. Values] field eg:

                   

                  Converting this:

                  When 1 Then Total(Sum([Pillar 0]))

                  to

                  When 1 Then Total(Sum(-[Pillar 0]))

                   

                  Steve

                  • 6. Re: Waterfall with multiple Measures
                    Kiran K

                    Hi Steve,

                    Yes, need blue bars understand the business at each phase/level how they are doing. I have added negative to cost elements before reached you but as we have to show blue bars also so Running Sum not working here. I have tweaked Cumulative values calc as below and finally able to achieve my waterfall as expected.

                     

                    Anyway, thank you so much for your trick, it's Awesome !

                     

                    IF INDEX() = 1  THEN  ([02. Values]) // p0

                    ELSEIF INDEX() = 2 THEN Running_Sum(([02. Values])) //c1

                    ELSEIF INDEX() = 3 THEN (([02. Values])) //p1

                    ELSEIF INDEX() = 4 THEN  PREVIOUS_VALUE(3) //c2

                    ELSEIF INDEX() = 5 THEN  (([02. Values])) //p2

                    ELSEIF INDEX() = 6 THEN  PREVIOUS_VALUE(5) //c3

                    ELSEIF INDEX() = 7 THEN  (([02. Values])) //p3

                    ELSEIF INDEX() = 8 THEN  Running_Sum(([02C5. Values])) //c4

                    ELSEIF INDEX() = 9 THEN   Running_Sum(([02C5. Values]))  //c5

                    • 7. Re: Waterfall with multiple Measures
                      Steve Martin

                      That's great Kiran, glad to see you are using the Previous_Value() function, which was my next suggestion to solving this.

                       

                      Very happy that this trick / work-around is working for you.

                       

                      Please can you mark your previous answer as correct if this is now resolved.

                       

                      Steve