Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
unnijoy
Post Partisan
Post Partisan

colour formating

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.

MonthJan-19 Feb-19 
Parameter NameTargetPerfoTargetPerfo
Ab5.00%6.50%5.00%4.30%
Att4.13%10.30%4.13%4.30%
JAR7.00%2.10%7.00%9.13%
ESAT100.00%100.00%100.00%89.00%
Aay Acc90.00%97.40%90.00%67.50%
First CR85.00%94.50%85.00%45.50%
IRT75.00%65.00%75.00%85.00%

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

Parameter NameCheck
AbLB
AttLB
JARHB
ESATHB
Aay AccLB
First CRHB
IRTHB

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.

MonthGeoCountryParameter NameTargetNumeratorDenominator
JanuaryLATAMBrazilAb5.00%14421425470
JanuaryLATAMJamaicaAb5.00%20111504372
JanuaryNACanadaAb5.00%29028729501
JanuaryNAUSAAb5.00%1514413770164
JanuaryLATAMBrazilAtt4.17%15508455330
JanuaryLATAMJamaicaAtt4.13%21544539704
JanuaryNACanadaAtt4.30%31239780745
JanuaryNAUSAAtt4.32%1624574034489
JanuaryLATAMBrazilJAR7.00%22254487280
JanuaryLATAMJamaicaJAR7.00%30833577509
JanuaryNACanadaJAR7.00%44853835578
JanuaryNAUSAJAR7.00%2327294317316
FebruaryLATAMBrazilAb5.00%16151404198
FebruaryLATAMJamaicaAb5.00%22525479154
FebruaryNACanadaAb5.00%32512693027
FebruaryNAUSAAb5.00%1696173581657
FebruaryLATAMBrazilAtt4.17%17370432563
FebruaryLATAMJamaicaAtt4.13%24130512718
FebruaryNACanadaAtt4.30%34988741708
FebruaryNAUSAAtt4.32%1819533832766
FebruaryLATAMBrazilJAR7.00%24925462917
FebruaryLATAMJamaicaJAR7.00%34532548634
FebruaryNACanadaJAR7.00%50235793800
FebruaryNAUSAJAR7.00%2606564101450
JanuaryLATAMBrazilESAT100.00%314
JanuaryLATAMJamaicaESAT100.00%57
JanuaryNACanadaESAT100.00%211
JanuaryNAUSAESAT100.00%121
JanuaryLATAMBrazilAay Acc90.00%43264534
JanuaryLATAMJamaicaAay Acc90.00%34564436
JanuaryNACanadaAay Acc90.00%55355876
JanuaryNAUSAAay Acc90.00%50675564
JanuaryLATAMBrazilFirst CR85.00%10598.710654.9
JanuaryLATAMJamaicaFirst CR85.00%8467.28650.2
JanuaryNACanadaFirst CR85.00%13560.7513808.6
JanuaryNAUSAFirst CR85.00%12414.1513075.4
JanuaryLATAMBrazilIRT75.00%17487.85520777.055
JanuaryLATAMJamaicaIRT75.00%13970.8816867.89
JanuaryNACanadaIRT75.00%22375.237526926.77
JanuaryNAUSAIRT75.00%20483.347525497.03

 Pelase help.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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.

test.PNG

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

View solution in original post

7 REPLIES 7
Mariusz
Community Champion
Community Champion

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.

image.png

  

image.png 

 

 

Best Regards,
Mariusz

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

Please feel free to connect with me.
Mariusz Repczynski

 

Anonymous
Not applicable

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.

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
Not applicable

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"
)

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
Not applicable

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.

test.PNG

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

Hi @Anonymous ,

 

Thank you verymuch... it is working now.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.