8 Replies Latest reply on Aug 15, 2018 8:00 AM by Joe Oppelt

    "Roll-up" data and recalculate result

    Christopher Ray

      Our datasets typically look something like this:

       

      Measure

      Doctor

      Numerator

      Denominator

      Result (Num/Denom*Multiplier)

      Multiplier

      1

      Jones

      1

      9

      1/9*100

      100

      2

      Jones

      3

      8

      3/8*100000

      100000

      3

      Jones

      6

      8

      6/8*100

      100

      1

      Ray

      22

      28

      22/28*100

      100

      2

      Ray

      88

      100

      88/100*100000

      100000

      3

      Ray

      12

      25

      12/25*100

      100

       

      For each measure, we would want to visualize a “rolled up” version of the measure’s performance. I.e., if I wanted to see how the entire enterprise is doing on Measure 1, we would combine Dr. Jones and Dr. Ray’s performance by adding both of their numerators and denominators and then recalculating the result by dividing the summed numerator and denominator.

       

      Example:

       

      Measure

      Doctor

      Numerator

      Denominator

      Result

      Multiplier

      1

      --

      1+22

      9+28

      23/50*100

      100

      2

      --

      3+88

      8+100

      91/108*100000

      100000

      3

      --

      6+12

      8+25

      14/37*100

      100

       

      I don’t know how to “roll-up” the data from it’s raw form and have the results recalculate. When adding the fields to a worksheet in Tableau, I only see options to sum the result, average, etc. I’ve been manually creating a separate data source of the rolled up data, but then it’s difficult to connect the various “levels” or the data since they’re all discrete sources.

        • 1. Re: "Roll-up" data and recalculate result
          Joe Oppelt

          { FIXED [Measure] : SUM([Numerator]) }

           

          this will sum up the numerators for each measure value (assuming [Measure] is a dimension.)

           

          Same for Denominator.


          The results of this can be treated as a dimension if you want.

          • 2. Re: "Roll-up" data and recalculate result
            Christopher Ray

            Hi Joe Oppelt,

             

            I'm not quite getting it. So, ultimately, I want my "Result" field to be dynamic with filters. It should always follow this general formula but should recalculate based on the currently filtered data: SUM(Numerator)/SUM(Denominator)*Multiplier

             

            I tried

             

            { FIXED [Measure] : SUM([Numerator])/SUM([Denominator[)} *[Multiplier]

             

            But it's only calculating the result on the full dataset, and is not dynamic with the filters I use.

            • 3. Re: "Roll-up" data and recalculate result
              Joe Oppelt

              Make two FIXED calcs.  One for numerator.  One for denominator.  It looks like the [Multiplier] is constant along a given measure, so you could probably just do MIN or AVG for that one, and not need the LOD.

               

              { FIXED [Measure] : SUM([Numerator]) }

               

              { FIXED [Measure] : SUM([Denominator]) }

               

              I'm suggesting, therefore, that your final calc would look something like this:

               

              [Numerator LOD calc]/[Denominator LOD calc] * MIN([Multiplier])

               

              Yes, regarding filters, usually they don't impact FIXED calcs.  Tableau's order of operation evaluates the FIXED LODs before filtering.  But order of operation shoves "in context" filters before doing FIXED LODs.  So right click on whatever filter(s) you want to impact these LODs, and select "Add to context".

              1 of 1 people found this helpful
              • 4. Re: "Roll-up" data and recalculate result
                Joe Oppelt

                BTW, you can nest LODs with others in one calc.

                 

                { FIXED [Measure] : SUM([Numerator]) } / { FIXED [Measure] : SUM([Numerator]) } * MIN([Multiplier]

                • 5. Re: "Roll-up" data and recalculate result
                  Christopher Ray

                  Hi Joe Oppelt,

                   

                  Thanks again for the help. I have a follow-up question for you. I'm doing something similar on another worksheet, but now I have an added "cut" of the data. Now the desired output looks something like this:

                   

                  Capture.JPG

                   

                  I have an added dimension in the columns to segment the data into the three "hubs". Each hub has their own numerator and denominator and calculated result. I now have date as a filter as well. The "Result" calculated field is still as follows:

                   

                  {FIXED [Measure]: SUM([Line Monthly Member Measure Numerator])/sum([Line Monthly Member Measure Denominator])}*[Multiplier]

                   

                  It is returning different results per individual measure (as expected), but the same result across each of the hubs. When changing { FIXED [Measure] to { FIXED [Hub Final], it does change the values, but still not the desired result (still the same value for each hub).

                  • 6. Re: "Roll-up" data and recalculate result
                    Joe Oppelt

                    You probably want to do

                     

                    { FIXED [Measure],[Hub Final] : ...

                     

                    And you mentioned a date filter.  By Tableau's Order Of Operations, FIXED calcs get evaluated before filters do.  But if you want the filters to impact the FIXED calcs, right click on the filter and select "Add to Context".  This moves the filter before the FIXED calcs in the order of operations.

                    1 of 1 people found this helpful
                    • 7. Re: "Roll-up" data and recalculate result
                      Christopher Ray

                      Thank you Joe Oppelt !!! This is working for all my sheets but one. On this one sheet, it's returning an asterisk for some results *.

                       

                      What does an * indicate?

                       

                      • 8. Re: "Roll-up" data and recalculate result
                        Joe Oppelt

                        You have different values per [Measure] in that cell.  Probably different [Hub Final]s  have different values.  Drag [Hub Final] behind the [Measures] pill on the columns shelf and see if there are different values.

                        1 of 1 people found this helpful