New Enterprise DNA Initiatives

Formatting column based on value AND another column

Data Visualizations
#1

Good morning,

Best explained with a picture. I need to format my variance column depending on if the account is an expense type or an income time. pbix attached. I’m sure I’ll need some kind of a measure… mmm. Could anyone please help me with this?

99
99737×271 10.7 KB
Q.16 Data.zip (33.6 KB)

As always, thank you
Michelle

#2

@michellepace
You can try this
Measure =
IF(LEN[Type]) = 6,
1,
0)
1 means income
0 means expenses

Then you can go to field formatting select Var column and go to conditional formatting and select rules and choose this Measure.
Assign a color to 1 and a different color to 0.
I am sorry not explaining with images as answering via mobile.

Hope it helps

#3

You can also try data bars in formatting section, you can choose different colurs for positive ad negative bars to make your column more readable.
try out this video by Sam.


Hope it helps.
#4

@michellepace,

Here’s a third way of doing this, that uses my preferred approach of conditional formatting via the Field Value option, and calls the color directly by name in the measure:

CF Variance =

VAR Cond1 = 
IF(
    AND(
        SELECTEDVALUE( 'Table'[Type] ) = "Expenses", SELECTEDVALUE( 'Table'[Variance] ) > 0
    ),
    1,
    0
) 

VAR Cond2 = 
IF(
    AND(
        SELECTEDVALUE( 'Table'[Type] ) = "Income", SELECTEDVALUE( 'Table'[Variance] ) < 0
    ),
    1,
    0
) 

VAR Result = Cond1 + Cond2 

VAR CF = IF( Result > 0, "Red", BLANK() ) 

RETURN
CF

image

I hope this is helpful. Full solution file attached below.

5 Likes
#5

@michellepace I totally missed the picture in your requirement.
@BrianJ solution is the best here. :+1:

#6

Hi @michellepace, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Also, 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!