1 of 1 people found this helpful
Create a second calculation that instead of setting the measures as strings, sets them as integers--then, the calculation will be a continuous measure.
IF [Actual Margin %]>[Budget Margin %] THEN 1
ELSEIF [Actual Margin %]<[Budget Margin %] THEN 2
ELSEIF [Actual Margin %]=[Budget Margin %] THEN 3
ELSE 0 END
By placing this new calculation on the level of detail shelf, then right click and select Continuous. Then, you'll have the option to filter. You can still use the original calculation on the label shelf.
Hope this helps a little bit!
The solution certainly worked - thank you! Which begs the next question: since the new calculation (on the level of details shelf) is all integers, the resulting filter that I see is a slider bar (0-3). Is there a way to convert it to checkboxes, or present it in a more meaningful way on the dashboard? Certainly the end users will not know what conditions do these integers 0,1,2 and 3 signify?
Any thoughts on it?
I would recommend making legend perhaps on a different sheet and adding it to the dashboard.
Although not very favorably received by my business team, I have gone ahead with the slider functionality with added description of labels in a separate Text Box.
Saad, here's a trick I learned from Tableau. In the "Actual Over Delivered" calculated field, if you wrap your current calculation in a lookup function then you will be able to directly put it on the filter shelf without having a legend.
For example, your new calc will be:
lookup(IF [Actual Margin %]>[Budget Margin %] THEN "Over"
ELSEIF [Actual Margin %]<[Budget Margin %] THEN "Under"
ELSEIF [Actual Margin %]=[Budget Margin %] THEN "Equal"
ELSE "N/A" END, 0)
Hope that helps.
Gordon, that's great. Thanks for sharing it Have you tried setting this new filter as Global? or does one have to be content with having a Local filter? Also, what does the "0" at the end signify? Can the Lookup function have any other value as well?
Plus, there's another observation which comes into play while you've created a custom hierarchy in the worksheet. (e.g. Continent-Country-State-City-County). Now, my calculated field above (using lookup) will only compute the two types of Margins per continent (the top level in hierarchy). If my user wished to see the total count of counties (the lowest level) where a particular condition is satisfied, they'd have to navigate at the lowest level before they can enable the option of computing using county.
Any thoughts on that?
Message was edited by: Saad Mazhar