Drop Var to Budget on color and play with the color settings until you get what you want.
How did you get the arrow? The method above is going to change all the numbers and the arrow based on the value in Var to Budget: I'd think you'd want to use a shape so that you can just control the arrow all by itself.
7 of 7 people found this helpful
Since the triangle is added in the custom formatting of "var to budget" (0.00% ▲;-0.00% ▼), it cannot be colored uniquely without affecting the number. However, if you want to color whole row number based on the positive or negative value of Var to Budget, you can put this measure to color pane, and set step to 2.
4 of 4 people found this helpful
I'm not aware of a way to change color for specific characters. One possible solution is to create a formula based on the Variance Indicator and color the rows accordingly. Please see details below.
Create the formula below:
IF [Transaction (Testing Data 15072018)].[Var to Budget]< 0 THEN 1
ELSEIF [Transaction (Testing Data 15072018)].[Var to Budget] > 0 THEN 2
Drag to Color and Make it Discrete and change color accordingly.
I hope this helps or gives you an alternative to your request.
Thank you all for your replies to my question. What I am trying to achieve is to show a green symbol when the 'var to budget' field is positive, and a red symbol if it is negative. I only want to have the symbol colour change.
I think my approach to achieving this is wrong. Can anyone explain to me how to achieve this?
Read Michael Ye's response carefully. You can't colour JUST the arrow the way you're doing it.
Instead, you need to use a SYMBOL to show the Up or Down arrow; then you can colour the symbol independently based on the value range.
On the Marks card, change the drop-down to Shape. A new mark will show up. Drag the 'var to budget' field onto that shape and pick the shape you want. Then drag the 'var to budget' field onto Color and pick the color range you want.
You'll likely want Red-Green Diverging, and you'll want to set it to be just 2 colors (default is 5).
1 of 1 people found this helpful
Michael Ye has the answer after testing it out. Put Var to Budget on color.
Then click Edit Colors... from the legend
Then check the box on Stepped Color and set it to 2.
Then click the dark red colored square on the left and set to the red you want, and the blue colored square on the right and set to the green you want.
To confirm it is set correctly for negative = red and positive = green, you can click Advanced >> and check the box for Center and set to 0.
Then you get the results you want, I think!
Another possibility is to pivot your view and create a secondary worksheet with only the Shapes and format accordingly. This way you can create a dashboard that (with the proper formatting) will appear to be united. Basically I copied your worksheet to keep all columns and rows consistent and hid all the data so filtering will remain consistent if you make them available. From there it's basically formatting. The below is still a little rough but should be enough to get the idea and know if it'll work for your purposes.
Shape_Formula_T1.twbx 592.6 KB
Thank you for your help on this query and your explainations on the solution. I really appreciate it.