
1. Re: Percentage not calculating correctly
ShivaRam Chennapragada Apr 19, 2018 4:25 PM (in response to Char Miller)1 of 1 people found this helpfulCould you modify your Collection Percentage to this and see if it works,
FLOAT(SUM([Payed]) + SUM([Ins Payed]))/SUM([Net])

2. Re: Percentage not calculating correctly
Mark Holtz Apr 19, 2018 8:38 PM (in response to ShivaRam Chennapragada)1 of 1 people found this helpfulI 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%)

3. Re: Percentage not calculating correctly
Char Miller Apr 20, 2018 9:12 AM (in response to Char Miller)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:

Percentage problem 2.csv.zip 368 bytes


4. Re: Percentage not calculating correctly
ShivaRam Chennapragada Apr 20, 2018 9:19 AM (in response to Char Miller)The calculation itself is valid, so could you try replacing the measure values?

5. Re: Percentage not calculating correctly
Char Miller Apr 20, 2018 9:44 AM (in response to ShivaRam Chennapragada) 
6. Re: Percentage not calculating correctly
ShivaRam Chennapragada Apr 20, 2018 10:53 AM (in response to Char Miller)1 of 1 people found this helpfulYes, each SUM ensures that individual rows for Payments and Net are aggregated individually before averaging.

7. Re: Percentage not calculating correctly
Mark Holtz Apr 20, 2018 11:39 AM (in response to Char Miller)1 of 1 people found this helpfulMaybe 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 senseyou'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 preaggregate 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 redrag 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."