1 Reply Latest reply on Aug 14, 2018 7:07 AM by Joshua Milligan

# "Roll-up" data and recalculate result

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

Christopher,

I'm guessing this is a Tableau Desktop question even though it somehow got categorized as a Tableau Prep question.  Assuming that, I would suggest creating a calculated field with code like this:

SUM([Numerator]) / SUM([Denominator])

As it uses an aggregation, the calculation is called an aggregate calculation and will be recomputed every time you change the level of detail in a view.  So, if you slice the data only by the Doctor, you'll get the results you described above.

Hope that helps!

Joshua