colour formating

[unnijoy]

Hi want a measure that can be used for colour formating based on Parameter and target. In our case some are like if the actual is below the target then it show green background and in some case if actual above the target then red. Below is the sample table.

Month	Jan-19		Feb-19
Parameter Name	Target	Perfo	Target	Perfo
Ab	5.00%	6.50%	5.00%	4.30%
Att	4.13%	10.30%	4.13%	4.30%
JAR	7.00%	2.10%	7.00%	9.13%
ESAT	100.00%	100.00%	100.00%	89.00%
Aay Acc	90.00%	97.40%	90.00%	67.50%
First CR	85.00%	94.50%	85.00%	45.50%
IRT	75.00%	65.00%	75.00%	85.00%

Below is the condetion table for referrence.(HB=Higher the better,LB=Lower the better)

Parameter Name	Check
Ab	LB
Att	LB
JAR	HB
ESAT	HB
Aay Acc	LB
First CR	HB
IRT	HB

Data base table is as below. Perfo is calculated by (Numerator/Denominator) and in the Matrix table we are taking target based on average of target.

Month	Geo	Country	Parameter Name	Target	Numerator	Denominator
January	LATAM	Brazil	Ab	5.00%	14421	425470
January	LATAM	Jamaica	Ab	5.00%	20111	504372
January	NA	Canada	Ab	5.00%	29028	729501
January	NA	USA	Ab	5.00%	151441	3770164
January	LATAM	Brazil	Att	4.17%	15508	455330
January	LATAM	Jamaica	Att	4.13%	21544	539704
January	NA	Canada	Att	4.30%	31239	780745
January	NA	USA	Att	4.32%	162457	4034489
January	LATAM	Brazil	JAR	7.00%	22254	487280
January	LATAM	Jamaica	JAR	7.00%	30833	577509
January	NA	Canada	JAR	7.00%	44853	835578
January	NA	USA	JAR	7.00%	232729	4317316
February	LATAM	Brazil	Ab	5.00%	16151	404198
February	LATAM	Jamaica	Ab	5.00%	22525	479154
February	NA	Canada	Ab	5.00%	32512	693027
February	NA	USA	Ab	5.00%	169617	3581657
February	LATAM	Brazil	Att	4.17%	17370	432563
February	LATAM	Jamaica	Att	4.13%	24130	512718
February	NA	Canada	Att	4.30%	34988	741708
February	NA	USA	Att	4.32%	181953	3832766
February	LATAM	Brazil	JAR	7.00%	24925	462917
February	LATAM	Jamaica	JAR	7.00%	34532	548634
February	NA	Canada	JAR	7.00%	50235	793800
February	NA	USA	JAR	7.00%	260656	4101450
January	LATAM	Brazil	ESAT	100.00%	3	14
January	LATAM	Jamaica	ESAT	100.00%	5	7
January	NA	Canada	ESAT	100.00%	2	11
January	NA	USA	ESAT	100.00%	1	21
January	LATAM	Brazil	Aay Acc	90.00%	4326	4534
January	LATAM	Jamaica	Aay Acc	90.00%	3456	4436
January	NA	Canada	Aay Acc	90.00%	5535	5876
January	NA	USA	Aay Acc	90.00%	5067	5564
January	LATAM	Brazil	First CR	85.00%	10598.7	10654.9
January	LATAM	Jamaica	First CR	85.00%	8467.2	8650.2
January	NA	Canada	First CR	85.00%	13560.75	13808.6
January	NA	USA	First CR	85.00%	12414.15	13075.4
January	LATAM	Brazil	IRT	75.00%	17487.855	20777.055
January	LATAM	Jamaica	IRT	75.00%	13970.88	16867.89
January	NA	Canada	IRT	75.00%	22375.2375	26926.77
January	NA	USA	IRT	75.00%	20483.3475	25497.03

 Pelase help.

[Mariusz]

Hi @unnijoy 

You can create a measure like below.

Colour Formating = INT( [Actuals] >= [Targets] )

And use it for conditional formatting, format by "Rule" as below.

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

[Anonymous]

If I understand your issue correctly then I would create a measure like the one below:

formatting = 
    IF(
        SELECTEDVALUE( Dim[Check]) = "LB";
        IF( // For LB
            [target] > [Perfo];
            "Green";
            "Red"
        );
        IF( // For HB
            [target] < [Perfo];
            "Green";
            "Red"
        )
    )

And then I would make some conditional formatting --> Based on field --> [formatting]

If this works then please mark it as the accepted solution.

[unnijoy]

Thanks for your reply, the table that i keept as referrence is not part of my data base. It is just for referrence. I need the dax baed on the first table.  somthing like if(and([parameter]="Ab"),Perfo<=avg[target]),"Green","Red") so it should be like nexted if. But in Power bi it is tough for make somthing like this. Can you help based on this.

[Anonymous]

I would make the table a part of you datamodel to make the most efficient measure.

But I guess you can make something like the measure below:

formatting = SWITCH( SELECTEDVALUE(Parameter),
"AB", IF( [Perfo] <= [Target], "Green", "Red"),
"ATT", IF( [Perfo] <= [Target], "Green", "Red"),
"ESAT", IF( [Perfo] > [Target], "Green", "Red"),
ETC...
"Black"
)

[unnijoy]

Hi @Anonymous , this is working. But when i try to use this measue for condetionakl formating the measure is not getting selected. Am i missing somthing. Please advice.

[Anonymous · Accepted solution]

When you have clicked on the conditional formatting then you should be able to change the format by to field value and then select your measure as the field like below.

If this works then please mark it as the accepted solution.

[unnijoy]

Hi @Anonymous ,

Thank you verymuch... it is working now.