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

# "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

{ 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

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

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

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

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: 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

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

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

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