7 Replies Latest reply on Apr 20, 2018 11:39 AM by Mark Holtz

# Percentage not calculating correctly

Hello -

I want to add a percentage to my view and I can't seem to get it to work. My set up is:

Create a calculated field (Net)

Create a second calculated field (collection percentage) as follows ((Payed + InsPayed) / Net) and express this as a percentage

Here's how it's laid out in my view so far. Only one percentage is calculating correctly:

Here is my calculated field. I'm aggregating by average:

I looked at the data behind this point:

I can see rows where the calculation isn't right, but I can't figure out what's going wrong:

I've attached the spreadsheet shown above as Excel if anyone wants to use it to upload to a workbook. Sorry, I'm not able to upload the entire workbook for security reasons.

If anyone can help me figure out what's happening here, I'd greatly appreciate it.

Cheers,

Char

• ###### 1. Re: Percentage not calculating correctly

Could you modify your Collection Percentage to this and see if it works,

FLOAT(SUM([Payed]) + SUM([Ins Payed]))/SUM([Net])

1 of 1 people found this helpful
• ###### 2. Re: Percentage not calculating correctly

I may be missing something, but I think this might just be an error with the parentheses.

Seems like you want ( [Payed] + [Ins Payed] ) / Net

In your row level example, the record has Payed = 29, Ins Payed = 0 and Net = 29

And you're expecting it to yield 100%.

29 + 0/29 = 29

But (29 + 0) / 29 = 1.00 (100%)

1 of 1 people found this helpful
• ###### 3. Re: Percentage not calculating correctly

Thank you both -

Mark, the parenthesis were indeed wrong - thank you. I think what I need next is some help figuring out how to deal with the nulls and/or zeros. Because it's aggregated by avg., I end up with 77% instead of 69%, which is what the actual payments/net would be.

I have the calculation wrapped in a ZN function. Is there something else I need to do to account for nulls?

Data behind the 77% is attached.

Shivaram, I tried updating the calculation per your formula and, unfortunately, got lot of red! Screenshot below.

Thanks again for the help,

Char

Updated calc field;

Avg. is 77% when s/b 69%

Data:

Very angry Tableau viz:

• ###### 4. Re: Percentage not calculating correctly

The calculation itself is valid, so could you try replacing the measure values?

• ###### 5. Re: Percentage not calculating correctly

It worked!

So, to be sure I understand: the calculation is summing each individual column first, then averaging?

Thanks so much!

• ###### 6. Re: Percentage not calculating correctly

Yes, each SUM ensures that individual rows for Payments and Net are aggregated individually before averaging.

1 of 1 people found this helpful
• ###### 7. Re: Percentage not calculating correctly

Maybe a bit more for your edification in case you didn't know already:

Often when doing calculations involving multiple measures (e.g., A as a % of B), you need to consider whether you will perform the calc at the row level and THEN aggregate the result

like AVG[A/B]  (***SUM in this case usually wouldn't make sense--you'd just end up with the SUM of all the individual % calculations of each row)

or if you want to aggregate A and THEN divide by an aggregate of B (e.g., SUM(A) / SUM(B) )

When you pre-aggregate within the definition of a calculation, using that calculation now only allows the AGG measure calculation when you drag that measure onto a Tableau View.

If you had A/B as a calculated field and dragged it onto your view as SUM([CalcField]), and then you changed the definition of [CalcField] to become SUM(A) / SUM(B), that's when Tableau gives you the "angry red pills." You are no longer allowed to represent SUM([CalcField]) but instead should re-drag the measure onto the view and will see Tableau refer to it as AGG([CalcField]). That's your clue that the definition of the calculation is already performing some sort of aggregation that could extend beyond the "row level."

1 of 1 people found this helpful