Just write a calculation:
IF [Score] < 0.8
ELSEIF [Score] >1.2
Put this calculation on the Color mark.
I know for a fact that this is incorrect as there are 300 Underweight and 149 Over weight records. I'm using CountD function in rows because this information is time sensitive so multiple records of the same person appear on different dates.
I'm guessing that Tableau might be aggregating the Efficiency Score for the same Name due to which its always Overweight?
If you can send me a sample workbook I can take a look at it.
seems like you are right
please try to sum and check the results
if sum([Efficiency Score]) < 0.8
elseif sum([Efficiency Score])>1.2 then 'Overweight'
The Efficiency Score is an aggregate function so I cannot put another aggregate on top of it
Let me clarify. The Efficiency score is a product of two ratios i.e x/y * a/b. Those ratios are also calculated using Tableau.
Yes you can formula in the above view because you are dis-aggregating the data with site id in the above view
Please provide some sample data if possible
Please find the data in the picture below:
Efficiency Score = Ratio Volume * Ratio Collections
Ratio Collections = Sum(#ofcollections)/Median Optimal Collections
Ratio Volume = Volume Collected(YDS)/Potential Volume Collected
Potential Volume Collected = Median Optimal Collections * Median Site Volume YDS