For the highlighted record, it's computed as:
(87,223 - 246,623)
-------------------- = -64.43%
You can drag the table calculated field into the measures, and edit it to view the formula used.
And you can modify it as required.
Do you want the value for Q1-Medicaid to be 18999/246623 ?
You don't want a table calc there.
You just want a calc that does SUM([High Risk)/SUM(Total Adult Population)
And that's not a table calc.
The quick table calc you show in your screen shot is giving you a percent change between Q1 and Q2. (And between Q2 and Q3. Etc.)
As Hari said, you can drag over the quick calculation;
I think it is probably taking the difference in percents and dividing it by the second percent.
I think you may be looking more for just the straight difference:
[% of Total Calc] - LOOKUP ( [% of Total Calc], -1 )
with a Compute Using of Table Across.
Please see workbook attached in Forum Thread.
273651tablecalc.twbx 24.1 KB
My concern is that it is giving me a percent change on the total population from quarter to quarter instead of giving me a percent change in the high risk in relation to the total population from quarter to quarter. Any ideas on how to do this?
I could not open your workbook. Not sure why. Unless it is a firewall issue.
Actually, it's an issue with using different versions. Mine is 10.2.10 64-bit.
For a better "Table Calculations" understanding, I also recommend to review the video tutorial from Tableau Learning* (*registration required) and a comprehensive article about Table Calculations below.
I hope this helps
Given the numbers in your screen shot, what are the numbers you want to see in the arithmetic for the first %change value? (You are using your business terms, which are second nature for you, but not for me. Tell me the specific numbers you want to be compared and included in the calculation for the first value, and where you want that first value to be.)
I'm not sure, but as an analogy, Total Population would be equivalent to a car dealership's total truck sales, and high risk would be equivalent to the sale of all trucks each quarter. The objective would be to know quarter by quarter the percentage of sales of 4wd trucks went up or down in relation to the total truck sales for that quarter. Does that help?
I think it would be taking the percentage difference between total truck sales and 4wd trucks for one quarter, then comparing it to the difference in the next quarter to determine if sales went up or down? Does that sound right?
I'm not looking for concepts. Tell me the physical numbers you want moved around. In your screen shot you have numbers. That's what I need you to use.
"I want 246,623 divided by 87,223, and 18,999 divided by 7,127, and I want the first of those two values divided by the second of those two values... And if we do it right, the result should be negative 41 percent..."
Something like that. You know your numbers. You know what you need as an output. What I can help you with is the proper syntax, and whether that needs to be a table calc or an aggregate calc.
I wish I had your workbook. I'd be able to know what is in the [% of Total Calc] for starters.
I would like 246,623 divided by 87,223 for Q1, then 18,999 divided by 7,127 for Q2, then determine the difference between those resulting values to determine if the percentage of the high risk went up or went down. Then do the same from Q2 to Q3, Q3 to Q4, then Q4 to Q1 of the following year, etc.
I can only email my workbook. Our firewall prevents me from uploading due to possible PHI violations. Although there is no PHI in this workbook, I still cannot upload, only email.
OK, so the [% Total Calc] should look like this:
sum([Total Adult Population])/SUM([High Risk])
When you put that on your sheet, it should go on as AGG(% of Total Calc). Not SUM(% of Total Calc) as your initial screen shot shows. You need to wrap the two measures in SUM() inside the % Total calc.
Once you have that, then your quick table calc doing Percent of Difference should give you what you need.
If you have to email, we can work that out. But for now let's see if we can get this working with what I stated above.