Flexible formatting

Data Visualizations
1

I have a lot of visuals and want to set a standard group of colours for formatting
Currently I use a variable called
format_Actual - this returns one of six hexcodes for colurs (based on hardcoded numbers)
positive numbers I want two shades of green
blank or zero get grey
negative numbers I want two shades of red (based on hardcoded numbers)

I want to get away from the hardcoded numbers.

Is there a better way than get
Mn = the min value for the measure from allselected
Mx = the max value for the measure from allselected

Bright Green is >Mx /2
Pale Green is >0
Bright Red is <Mn/2
Pale Red <0
Grey is BLANK()
Grey = zero

Hope that makes sense. If I could get away from the variables for each measure that would be fab,

Thanks
E

2

@ells,

If I’m understanding the question correctly, the way I would set this up is as a SWITCH( TRUE ) constract, like this:

Colors =
VAR Mn = CALCULATE( MIN ( field ), ALLSELECTED( field )
VAR MX = CALCULATE( MAX( field ), ALLSELECTED( field )
VAR HighPositive = "#xxxxxx"
VAR LowPositive = "#xxxxxx"
VAR ZeroColor = "#xxxxxx"
VAR HighNegative= "#xxxxxxx"
VAR LowNegative = "#xxxxxx"
VAR BlankColor = "#xxxxxx"
VAR Result =
SWITCH( TRUE(),
     SELECTEDVALUE( field ) > Mx/2, HighPositive,,
     SELECTEDVALUE( field ) > 0, LoePositive,,
     SELECTEDVALUE( field ) = 0, ZeroColor,,
     SELECTEDVALUE( field ) < Mn/2, LowNegative,,
     SELECTEDVALUE( field ) <  0, HighNegative,,
     BlankColor
)

RETURN Result

This assumes the min harvested value can be negative. If not, you will need to alter the order above, but you get the gist.

This construct lets you easily change the colors just by altering the variable values. Alternatively, if you’ll be using these colors in multiple measures, create a measure for each color, and then in the variable section, reference that measure instead of a hex code. This way, if the color changes, you only have to chancge it once in the measure, and that change will roll down through all the other measures automatically via measure branching.

I hope this is helpful.

  • Brian
4 Likes
3

@BrianJ
Thanks. Thats where I was going to. Measure for each colour now thats saved mne some work.

I just hate the way you have to have a fixed number in the conditional formatting. As soon as the users filter the selection it just stops working properly

Thanks
E

4

Sorry, I’m not following. If you set your measures up this way and use field-based conditional formatting, it should stilll work even if the user filters the range.

  • Brian
1 Like
5

@BrianJ
Sorry. I hate the out of the box way of fixing a number.
So one one of my Matrix charts the year value may be £1M and so gets coloured green (using the rules option) . If the users filter the page to current month then the number drops to £80,000 and that colour scheme does not work as expected as its is still looking for the hardcoded number.

Using a Measure for the format is so much better
Thanks

6

Is there a way of doing this on a measure rather than field?

I have a matrix similar to
Country in Columns
Year on Rows
Sales as Value

As I want to reuse this formatting measure I dont want to make a measure for each specific matrix
I am hoping to set this as a standard

Thanks
E

7

@ells,

I think that would be possible, but at this point it would be best if you could please provide a sample PBIX, so that we can test specific solutions rather than just talking in the abstract. Thanks.

  • Brian
8

@ells,

Okay, thanks for the clarification- I see your issue here. This should be pretty straightforwardly addressable using a SWITCH (TRUE()) construct together with ISINSCOPE.

I almost never do conditional formatting that’s not controlled by a measure. Just so much easier, more flexible and simpler to make changes.

  • Brian
9

Hi @ells, did the response provided by @BrianJ help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query.

I hope that you are having a great experience using the Support Forum so far. We’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!