Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I've got 2 measures. Headcount actual & Headcount required. I'm displaying them in a pivot table, when I chose headcount required as my measure. The background color expression =IF(([Headcount Actual])>=([Headcount Required]),'Green',red()) works fine. But if I change my measure to concatenate the 2 fields using this expression, ([Headcount Required]) & ' / ' & [Headcount Actual], it seems to ignore the "=>" part.
When the fields are not concatenated, any measure that shows as equal to or greater turns green. When I concatenate the fields to show 2 / 2, it is only coloring values that are greater than as green, and values that are equal as red. Photos below to help show my explanation.
Any help on this please? I've checked and there are no decimal places, only whole numbers.
You want to compare the combined string of [Headcount Required] & ' / ' & [Headcount Actual] numerically against what?
I'm comparing Headcount Required / Headcount Actual against each other, the expression works fine when I don't inlcude the headcount actual, but if its red, I'd like to show how much they are missing. Please let me know if that doesn't make sense!
If you want divide both information then you may try it in this way:
IF([Headcount Actual] / [Headcount Required] >= 1,Green(),red())
There seems to be a misunderstanding about what I'm trying to achieve and that is probably down to me using
" / " as a value separator!
I'm trying to show a comparison between the 2 values, I'm not trying to divide them. So if my required headcount is 2, and my actual headcount is 2, that should be green. But If I concatenate both measures into the same pivot table column, it will show 2 | 2 as red rather than green. If I don't concatenate both measures, it shows it as green.
To color anything based on a condition means to make a numerically comparison, like the above mentioned ones. But this doesn't mean to display this comparison also as result within the object - else it are different properties which need different measures.
So as measure of the columns you could use:
[Headcount Actual] & ' / ' & [Headcount Required]
and as attribute-expression to control the coloring something like:
IF([Headcount Actual] / [Headcount Required] >= 1,Green(),red())
So my column measure is [Headcount Actual] & ' / ' & [Headcount Required].
My background color expression is.
=IF(([Qualified Technician Headcount Actual])>=([Qualified Technician Headcount Required]),'Green',red())
As I said above, when I have the column measure just as [Headcount Actual]. The background color measure works fine and displays 2 as green, but if I change the column measure to combine 2 columns into one, it doesn't work correctly and will show the 2 as red.
Please elaborate your approach in more details. Are the headcounts native fields or expressions? Should the combination of them be done within a dimension or an expression? Are there really multiple fields of actual/required headcounts, like: [Qualified Technician Headcount Actual] and [Headcount Actual] and how are they connected?