5 Replies Latest reply on Feb 17, 2016 4:35 AM by Simon Runc

Calculate new measure as a percentage of another

I have two measures, call them [Joiners] and [Leavers] I want to show [Leavers] as a percentage of [Joiners], so [Leavers]/[Joiners].  Fair enough.

But my data is stored by date and I'm displaying by month.

So the percentage calculation gets delegated to the database and then summed, which is obviously wrong.

How do I calculate the percentage in context and not delegated to the database row?

• 1. Re: Calculate new measure as a percentage of another

hi John,

I think from your description here, you need to do the aggregation in the calculated field (as you would do for something like Margin %, in my retail world).

So your formula would be

SUM([Leavers])/SUM([Joiners])

This should then be correct and whatever level you are looking at.

Let me know if this doesn't do the trick (or make sense!).

• 2. Re: Calculate new measure as a percentage of another

Grays out entire worksheet.

Refuses to respond.

• 3. Re: Calculate new measure as a percentage of another

As this is now an AGGREGATE calculation, and you're previous calculation was ROW LEVEL and then AGGREGATED (as a SUM/AVG), you may need to remove the field and re-drag in (currently it's trying to do an AGGREGATE on and AGGREGATE, which I think is throwing the error). If that doesn't do the trick....Is there anyway you can post a bit of the data (anonymized if sensitive) so I can see what's going on. The 'Shape'/'Grain' of the data is important in knowing what the best approach is.

• 4. Re: Calculate new measure as a percentage of another

Hi Simon

That was on the money.  Thanks very much.

John

• 5. Re: Calculate new measure as a percentage of another

Excellent news!

Well worth watching the 2 vids on ROW LEVEL and AGGREGATE Calculations (they are only a few mins each)

Tableau Training and Tutorials