Using the attached file. Some states have values for multiple populations (ex: Alabama & Iowa for Adolescent Well-Care Visit Ages 12-21). I need a calc that says "If the state has a score for Medicaid & CHIP use it, if not then if it has a score for Medicaid only use it, if not then if it has a score for CHIP only use it." So in the attached dashboard, the value for Alabama should be 42.2 instead of 74.1 (adding Medicaid Only & CHIP only).

I created some LODs that evaluate for each state what is in the various [Population] values. See [Medicaid & CHIP], [Medicaid], and [CHIP].

Then I made [Calc 1] that uses these three calcs to decide what to display in Calc1.

Notice that on Sheet 1 the value for Calc1 displays where any actual value for [State Rate] displays. I fixed that on sheet 1(2).

Also, the operation of a FIXED LOD ignores filters on the sheet unless the filter is moved to "in context". (Right click on the filter and select "add to context".) When you do this, the pill for the filter changes to grey so you can tell that something special is there.

On Sheet 1(2) I just display Calc 1, and I removed [Population] entirely. The LODs take care of looking at the individual population values, so we no longer need it here.

We actually could nest all those LODs into one calc rather than break them out as individual LODs. I generally like to break them out though, just because the "wordiness" of all that shoved into one calc can get confusing. And if some subsequent person ever has to modify something in this workbook, It will be far easier for him/her to figure out what's going on if you compartmentalize the sub-steps in individual calcs.

You need to unpivot the data basically. I have unpivoted for the 3 values you have mentioned using calc columns. I would suggest use the tableau unpivot feature to do that. Then create a calc column to choose 1 out of 3 values. See the attached worksheet.