Conditiional formatting on calculated columns

[h4_dkj]

Hi

I have created two different meassuers

#1

KPI_3_1_Budget =
if('BUDGET_COMMITMENTS (2)'[ACCOUNT] >= 3000,
f('BUDGET_COMMITMENTS (2)'[ACCOUNT] <= 7999,
if('BUDGET_COMMITMENTS (2)'[CODE_B] = 430,
IF(
'BUDGET_COMMITMENTS (2)'[BUDGET_VERSION]="B",
IF(
'BUDGET_COMMITMENTS (2)'[SIMULATION_VOUCHER]="FALSE",
'BUDGET_COMMITMENTS (2)'[BUDGET_AMOUNT],0)))))

#2

KPI_3_4_Real =
if('BUDGET_COMMITMENTS (2)'[ACCOUNT] >= 3000,
if('BUDGET_COMMITMENTS (2)'[ACCOUNT] <= 7999,
if('BUDGET_COMMITMENTS (2)'[CODE_B] = 430,
'BUDGET_COMMITMENTS (2)'[AMOUNT_BALANCE],
0)))

This all works just fine and I want my visual to look like the on below

Month	Real	Budget
1	155 000	150 000
2	147 000	150 000
3	162 000	157 000

I have tried using conditional formatting but I cannot get it right.  Any comments or ideas?

[v-yalanwu-msft]

Hi, @h4_dkj ;

Try to create a measure :

condition = IF([4_Real]<[1_Budget],"red","green")

then set conditional format.

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[h4_dkj]

Thanks for your input, that allmost solves it but when I implement it it is either all green or all read.

And I cannot figure out why 🙂

[v-yalanwu-msft]

Hi, @h4_dkj ;

Oh, Too little information is provided, it is difficult to know the reasons for all green or all red, can you share more information or simple pbix example after removing sensitive information?

[h4_dkj]

Hi

Abit difficult to reduce it to non-sensitive.

But I had a look at my dataset.

Using that condition formula I end up flagging every row in my dataset with either green or red flag. 

The dataset I work with look something like this:

Periodkey	Account	Cost	Budget	rowid
20214	3000	0	500	1
20214	3001	0	650	2
20215	3000	0	550	3
20215	3001	0	665	4
20214	3000	450	0	5
20214	3001	652	0	6
20215	3000	550	0	7
20215	3001	663	0	8
20216	5000	0	-125	9
20216	5001	0	-200	10
20216	5000	-126	0	11
20216	5001	-198	0	12

The trouble now is that the algined data for 20214 account 3000, row 1 and 5 would be red as we have earned less then the budget. To complicate it even further

The algined data for 20216 account 5000, row 9 & 11 would the tell us the we have earned more then budget and hence should be green.

So in order for my condition to work it would need to evalute the sum in my visual rather then each post seperately.

[v-yalanwu-msft]

Hi, @h4_dkj ;

According to your simple data, you could create a measure as follows:

condition =
IF (
    SUMX (
        FILTER (
            ALL ( 'Table' ),
            [Account] = MAX ( [Account] )
                && [Periodkey] = MAX ( [Periodkey] )
        ),
        ABS ( [Cost] ) - ABS ( [Budget] )
    ) < 0,
    "red",
    "green")

In addition, ABS() can be deleted according to your needs. The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[h4_dkj]

Hi

I just tried to create the following measure

KPI_Consition =

IF (

SUMX (

FILTER (

ALL ( 'BUDGET_COMMITMENTS (2)' ) [ACCOUNT] = MAX ( [ACCOUNT] ) && ( [PeriodKey] ) = MAX ( [PeriodKey] )

),

ABS ( 'BUDGET_COMMITMENTS (2)'[KPI_1_1_Virkelig] ) - ABS ( 'BUDGET_COMMITMENTS (2)'[KPI_1_1_Budsjett] )

) < 0,

"red",

"green")

It fails saying:

Syntax for [ACCOUNT] is wrong. (DAX(IF ( SUMX ( FILTER ( ALL ( 'BUDGET_COMMITMENTS (2)' ) [ACCOUNT] = MAX ( [ACCOUNT] ) && ( [PeriodKey] ) = MAX ( [PeriodKey] ) ), ABS ( 'BUDGET_COMMITMENTS (2)'[KPI_1_1_Virkelig] ) - ABS ( 'BUDGET_COMMITMENTS (2)'[KPI_1_1_Budsjett] ) ) < 0, "red", "green"))).

[v-yalanwu-msft]

Hi, @h4_dkj ;

There is one missing "," symbol is shown below:

KPI_Consition =
IF (
    SUMX (
        FILTER (
            ALL ( 'BUDGET_COMMITMENTS (2)' ),
            [ACCOUNT] = MAX ( [ACCOUNT] )
                && ( [PeriodKey] ) = MAX ( [PeriodKey] )
        ),
        ABS ( 'BUDGET_COMMITMENTS (2)'[KPI_1_1_Virkelig] )
            - ABS ( 'BUDGET_COMMITMENTS (2)'[KPI_1_1_Budsjett] )
    ) < 0,
    "red",
    "green"
)

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[h4_dkj]

I found it just before you replied, thanks.

However, it still does not do as I need it to.

It shows green even when it should not

Column 1 should be red, rest should be green

[v-yalanwu-msft]

Hi, @h4_dkj ;

It is difficult to find out the reason, because the simple data you gave me and tested correct. If possible, you can only make a virtual data by your data . and  ensure that the structure and type of the field and table are the same? then use dax tested it ,  If you are wrong, please share with me virtual data .

[h4_dkj]

Would you be up for a webex or teams tomorrow or friday?

[v-yalanwu-msft]

Hi, 

I'm sorry that we only support Community at present. Due to the internal regulations of the company, we do not support other ways of communication in order to ensure the security of customer information. so sorry.

Best Regards,
Community Support Team_ Yalan Wu

[h4_dkj]

okey, since I am new to PBI, please provide a step by step list in order to create a virtual copy and I will try that

[v-yalanwu-msft]

Hi,

Copy the original PBIX file, and then replace(change)  the real data. If the data is large, you can delete some data.

[h4_dkj]

I am ready to upload a test version of the PBIX

[v-yalanwu-msft]

Hi, @h4_dkj ;

if you can't  find any way of attaching it to this post, you could share excel  or image.

[amitchandak]

@h4_dkj , you can do rule base conditional formatting . Where you can use [real] <150000 red.

Of use color measure with filed value option

Switch( True(),

[Real] <150000, "red",

"green"

)

How to do conditional formatting by measure and apply it on pie?: https://youtu.be/RqBb5eBf_I4

[h4_dkj]

But does that support varians of the target. My budget shows that for month 3 we have estaimated a higher amount then 150 000, so hard coding a number want help me.