DAX for conditional formatting for font color

[third_hicana]

Hi Everyone. Asking for your help on conditional formatting for font color.

I want to highlight with red color if the 'Value' of each 'money_type' category of projects (those BOP IDs with suffix) and with 'BOP Tagging' equal to Project exceeds the budget of BOP.

For Example

For BOP25-018, it should highlight as red since BOP25-018-B with  capex, opex and total of 85, 20.50 , 105.50 respectively exceeds the budget of BOP25-018 with budget 65, 2 and 67 respectively.

While for project BOP25-035-B and BOP25-035-c should NOT be highlighted as red since the totals of these projects does not exceed  to the BOP budget of BOP25-035.

I tried this DAX but it also highlights the project budgets that does not exceed to the BOP budget.

VAR projects_tagging = SELECTEDVALUE('Projects Table'[BOP Tagging])

VAR moneyType = {"CAPEX", "OPEX", "TOTAL"}

RETURN

IF(

    projects_tagging = "Project" &&

    SELECTEDVALUE('Projects Table'[money_type])

    IN moneyType

    && 'Projects Table'[Project Refined Budget] > 'Projects Table'[BOP Refined Budget],

    "Red"

)

Here' the sample data. Thank you for your help

New BOP ID	money_type	Value	BOP Tagging
BOP25-035	TOTAL	£550.00	BOP
BOP25-035	CAPEX	£350.00	BOP
BOP25-035	OPEX	£200.00	BOP
BOP25-018	TOTAL	£67.00	BOP
BOP25-018	CAPEX	£65.00	BOP
BOP25-018	OPEX	£2.00	BOP
BOP25-035-B	TOTAL	£30.00	Project
BOP25-035-B	CAPEX	£10.00	Project
BOP25-035-B	OPEX	£20.00	Project
BOP25-018-B	TOTAL	£105.50	Project
BOP25-018-B	CAPEX	£85.00	Project
BOP25-018-B	OPEX	£20.50	Project

[ValtteriN]

Hi,

To troubleshoot you should add Projects Table'[Project Refined Budget] and 'Projects Table'[BOP Refined Budget] as variables. 

So,

var _BOP_B = SELECTEDVALUE('Projects Table'[BOP Refined Budget])
var _ P_B = SELECTEDVALUE('Projects Table'[Project Refined Budget]).

Now test your filter context like this:
BOP test 

VAR projects_tagging = SELECTEDVALUE('Projects Table'[BOP Tagging])
VAR moneyType = {"CAPEX", "OPEX", "TOTAL"}
var _BOP_B = SELECTEDVALUE('Projects Table'[BOP Refined Budget])
var _ P_B = SELECTEDVALUE('Projects Table'[Project Refined Budget]).

RETURN
/*
IF(
    projects_tagging = "Project" &&
    SELECTEDVALUE('Projects Table'[money_type])
    IN moneyType
    && 'Projects Table'[Project Refined Budget] > 'Projects Table'[BOP Refined Budget],
    "Red"
)
*/

_BOP_B

And the same for the other

VAR projects_tagging = SELECTEDVALUE('Projects Table'[BOP Tagging])
VAR moneyType = {"CAPEX", "OPEX", "TOTAL"}
var _BOP_B = SELECTEDVALUE('Projects Table'[BOP Refined Budget])
var _ P_B = SELECTEDVALUE('Projects Table'[Project Refined Budget]).

RETURN
/*
IF(
    projects_tagging = "Project" &&
    SELECTEDVALUE('Projects Table'[money_type])
    IN moneyType
    &&
_BOP_B

 >

_P_B

,
    "Red"
)
*/

_P_B

This way you can see if your calculation for the budgets is behaving as expected. Now if the values don't mathc your expetations. E.g. if BOP25-035 CAPEX is not 350. You can change the variable's calculation until the result is as expected.

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

[third_hicana]

Hi @ValtteriN 
Thanks for your response. However, the 

'Projects Table'[BOP Refined Budget] and 'Projects Table'[Project Refined Budget] are measures in my table. Its does not work in SELECTED VALUE function on my end.

[ValtteriN]

Hi,
In that case you can just test the measures the main point is to see if the measures are returning expected valeus to meet the IF conditions.