For (1), you can create a calculated field:
performance min/max =
(SUM([Performance]) = WINDOW_MAX(SUM(Performance)))
(SUM([Performance]) = WINDOW_MIN(SUM(Performance)))
Then add this field to the size shelf for the second set of marks, SUM(Performance) 2.
Next change mark type to square. You see big and small squares. Click on the small "False" square in the size legend and select hide. Then click on size to make the True marks bigger.
You're objective in (2) is at odds with (1), but first to get the result you want in (2), you need to use aggregates, which you probably want to be using regardless---your measures in the view are aggregates.
To make Color - YoY Perfomance an aggregate, just wrap each Performance measure with SUM().
Color - YoY Performance NEW =
If SUM([Performance]) >= SUM([Goal]) then "green"
Elseif SUM([Performance]) <= SUM([Goal])*.5 then "red"
But you can't have three mark types (area, line and square) in this graph. Option 1 gives you something close to your example png. You could enhance this with reference bands that colors the background red/green/yellow. Or you could add Performance min-max to the line size to emphasize these points.
Dashboard_jimw.twbx.zip 104.3 KB
1 of 1 people found this helpful
One other key point for (1). When you add performance min/max, you'll see multiple marks for min max.
What's happening is that the table calc is partitioning on all the values dimensions in the view, which includes your Color - YoY Performance dimension. You're getting a min/max for each color.
You could fix this with an advanced compute using, but I think it's better to just change the Color - YoY to an aggregate, as mentioned above. Now the field is no longer a dimension and the table calc won't be partitioned over it.
Jim, your help is spot on - thanks so much!