cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Column chart visual data color conditional formatting not working when slicing the visual

I have a line and stakced column visual with availability % per asset. I have applied conditional formatting to the data colors and formatted by rules; with the rules being:

  • If availability < 90% then color the bar red
  • If avalability >= 90% then color the bar green.

This works on the visual if there are no slicers, but I have slicers for equipment type, location, etc. As soon as I apply one of these slicers the data colors no longer follow the conditional formatting rule. 

 

I'm not sure if it matters, but I use a measure to calculate the availability: availability = (SUM(ScheduledHours) - SUM(DownTime)) / SUM(Scheduledhours).

 

I'd like to try correct the first issue, but going forward I use the line values as the Target Availability which varies between equipment, so ideally I would like the bar to be red if it falls under this Target Availability line and green if it is over the line.

 

1 ACCEPTED SOLUTION

@Bquinn It seems that we have a bug here or the percentage is calculated in a way I don't understand.

 

For the others what have I done - I changed the formatting rules and used numbers instead of percentages (0, 0.9, 1).

It worked then...

 

BR,

Josef

View solution in original post

12 REPLIES 12
Solution Sage
Solution Sage

Would you please provide us some copyable sample data?

 

BR,

Josef

Hi @JosefPrakljacic ,

 

doesn't seem like I can attach the file? Instead, see below some sample data below.

 

EquipmentDetails Table:

EqpDateKeyUsage_DateEquipmentEqpdescriptionSiteFleetKeyFleetEqpGroupModelActual_UsageScheduled_Hours
1074 - 2019-06-1414-06-2019 00:00:00WT2066WATER TRUCK - CAT 777F (91t) - JRP03031Saraji Rental367SB-Water CartWater Truck777F-WT0.07430340624
1074 - 2019-06-1515-06-2019 00:00:00WT2066WATER TRUCK - CAT 777F (91t) - JRP03031Saraji Rental367SB-Water CartWater Truck777F-WT0.07430340624
1074 - 2019-06-1616-06-2019 00:00:00WT2066WATER TRUCK - CAT 777F (91t) - JRP03031Saraji Rental367SB-Water CartWater Truck777F-WT0.08922600624
1074 - 2019-06-1717-06-2019 00:00:00WT2066WATER TRUCK - CAT 777F (91t) - JRP03031Saraji Rental367SB-Water CartWater Truck777F-WT0.79058823524
1074 - 2019-06-1818-06-2019 00:00:00WT2066WATER TRUCK - CAT 777F (91t) - JRP03031Saraji Rental367SB-Water CartWater Truck777F-WT2.41438938924
1074 - 2019-06-1919-06-2019 00:00:00WT2066WATER TRUCK - CAT 777F (91t) - JRP03031Saraji Rental367SB-Water CartWater Truck777F-WT7.28579285124
1074 - 2019-06-2020-06-2019 00:00:00WT2066WATER TRUCK - CAT 777F (91t) - JRP03031Saraji Rental367SB-Water CartWater Truck777F-WT7.28579285124
1074 - 2019-06-2121-06-2019 00:00:00WT2066WATER TRUCK - CAT 777F (91t) - JRP03031Saraji Rental367SB-Water CartWater Truck777F-WT7.28579285124
1074 - 2019-06-2222-06-2019 00:00:00WT2066WATER TRUCK - CAT 777F (91t) - JRP03031Saraji Rental367SB-Water CartWater Truck777F-WT4.3308241624
1074 - 2019-06-2323-06-2019 00:00:00WT2066WATER TRUCK - CAT 777F (91t) - JRP03031Saraji Rental367SB-Water CartWater Truck777F-WT0.88706365524
1074 - 2019-06-2424-06-2019 00:00:00WT2066WATER TRUCK - CAT 777F (91t) - JRP03031Saraji Rental367SB-Water CartWater Truck777F-WT5.43080835724
1074 - 2019-06-2525-06-2019 00:00:00WT2066WATER TRUCK - CAT 777F (91t) - JRP03031Saraji Rental367SB-Water CartWater Truck777F-WT6.78165137624
1074 - 2019-06-2626-06-2019 00:00:00WT2066WATER TRUCK - CAT 777F (91t) - JRP03031Saraji Rental367SB-Water CartWater Truck777F-WT6.78165137624
1074 - 2019-06-2727-06-2019 00:00:00WT2066WATER TRUCK - CAT 777F (91t) - JRP03031Saraji Rental367SB-Water CartWater Truck777F-WT6.78165137624
1074 - 2019-06-2828-06-2019 00:00:00WT2066WATER TRUCK - CAT 777F (91t) - JRP03031Saraji Rental367SB-Water CartWater Truck777F-WT6.78165137624
1074 - 2019-06-2929-06-2019 00:00:00WT2066WATER TRUCK - CAT 777F (91t) - JRP03031Saraji Rental367SB-Water CartWater Truck777F-WT6.78165137624
1074 - 2019-06-3030-06-2019 00:00:00WT2066WATER TRUCK - CAT 777F (91t) - JRP03031Saraji Rental367SB-Water CartWater Truck777F-WT6.78165137624
1074 - 2019-07-0101-07-2019 00:00:00WT2066WATER TRUCK - CAT 777F (91t) - JRP03031Saraji Rental367SB-Water CartWater Truck777F-WT0.38923547424
1074 - 2019-07-0202-07-2019 00:00:00WT2066WATER TRUCK - CAT 777F (91t) - JRP03031Saraji Rental367SB-Water CartWater Truck777F-WT0.11130434824
1074 - 2019-07-0303-07-2019 00:00:00WT2066WATER TRUCK - CAT 777F (91t) - JRP03031Saraji Rental367SB-Water CartWater Truck777F-WT0.11130434824
1074 - 2019-07-0404-07-2019 00:00:00WT2066WATER TRUCK - CAT 777F (91t) - JRP03031Saraji Rental367SB-Water CartWater Truck777F-WT0.11130434824
1074 - 2019-07-0505-07-2019 00:00:00WT2066WATER TRUCK - CAT 777F (91t) - JRP03031Saraji Rental367SB-Water CartWater Truck777F-WT0.11130434824
1074 - 2019-07-0606-07-2019 00:00:00WT2066WATER TRUCK - CAT 777F (91t) - JRP03031Saraji Rental367SB-Water CartWater Truck777F-WT0.11130434824

 

DailyDowntime Table:

EqpDateKeyEquipmentKeyDownHrsDailyDownTimeHrs
1074 - 2019-06-25107444
1074 - 2019-06-26107433
1074 - 2019-06-2810740.50.5
1074 - 2019-07-14107411
1074 - 2019-07-2310741818
1074 - 2019-07-24107444
1074 - 2019-07-28107444
1074 - 2019-08-16107488
1074 - 2019-08-17107466
1074 - 2019-09-05107466
1074 - 2019-09-0610742424
1074 - 2019-09-0710742424
1074 - 2019-09-08107400
1074 - 2019-09-09107466
1074 - 2019-09-10107400
1075 - 2019-06-18107512.6166667912.61666679
1075 - 2019-06-2010750.50.5
1075 - 2019-06-2910751.51.5
1075 - 2019-07-02107517.9999998217.99999982
1075 - 2019-07-03107513.333333213.3333332
1075 - 2019-07-1710755.5833333435.583333343
1075 - 2019-07-1810752.252.25
1075 - 2019-07-1910750.250.25
1075 - 2019-07-26107516.8333338416.83333384
1075 - 2019-07-2710754.5000001344.500000134
1075 - 2019-08-0810750.50.5
1075 - 2019-08-1310750.6666666870.666666687

 

Events/Breakdowns Table:

EqpDateKeyEqp_Plan_IDEvent_IDActual_Down_TimeActual_Up_TimeDownTimeDescriptionBreakdown
1074 - 2019-06-20107410714200:00.000:00.00WT2066: Steering Components Inspection - NDT ( PO 52384 )FALSE
1074 - 2019-06-25107410683100:00.000:00.0420WT2066: BLOWN A/C COMPRESSORTRUE
1074 - 2019-06-28107410681215:00.045:00.030WT2066 - FIRE SUPPRESSION INSPECTION - 6 MONTHLY (L1) Dont move FSI are bookedFALSE
1074 - 2019-07-14107410773000:00.000:00.060WT2066 - Sprays not workingTRUE
1074 - 2019-07-23107410505100:00.000:00.01320WT2066: 500 HOUR SERVICE - KITFALSE
1074 - 2019-07-28107410843730:00.030:00.0240WT2066 - Wont startTRUE
1074 - 2019-07-31107410852600:00.000:00.00WT2066: C SAMPLE WHEEL HUB LEFTTRUE
1074 - 2019-08-16107410940100:00.000:00.0120WT2066 Brake Test - Fully LoadedFALSE
1074 - 2019-08-16107410505600:00.000:00.0720WT2066: 250 HOUR SERVICE /FALSE
1074 - 2019-09-03107411051800:00.000:00.00WT2066: Site Compliance Inspection (FRC)FALSE
1074 - 2019-09-05107410956800:00.000:00.03240WT2066: 2000 HOUR SERVICE - KIT/ Hoses 24 are booked in for the 06/09/2019 6am start.FALSE
1074 - 2019-09-09107411093700:00.000:00.0360WT2066 Horn not workingTRUE
1074 - 2019-09-12107411107718:00.018:00.00WT2066 Steering Components Inspection - NDT PO 58905FALSE
1075 - 2019-06-18107510646045:00.022:00.0757Coolant C SampleTRUE
1075 - 2019-06-20107510656200:00.030:00.030Low coolantTRUE
1075 - 2019-06-29107510698800:00.030:00.090Aircon fan U/STRUE
1075 - 2019-07-02107510654700:00.020:00.01880DT2630: 250 Hour ServiceFALSE
1075 - 2019-07-17107510784200:00.015:00.0315Fire suppression faultFALSE
1075 - 2019-07-17107510790100:00.020:00.020Air startFALSE
1075 - 2019-07-18107510791645:00.000:00.0135Lost cab powerFALSE
1075 - 2019-07-19107510795120:00.035:00.015Start faultFALSE
1075 - 2019-07-26107510652710:00.030:00.01280DT2630: 2000 Hour ServiceFALSE
1075 - 2019-08-08107510909445:00.015:00.030Air StartTRUE
1075 - 2019-08-13107510933250:00.030:00.040Door Alarm Staying onTRUE
1075 - 2019-08-19107510969100:00.055:00.055TC Filter PluggedTRUE
1075 - 2019-08-21107510866600:00.000:00.0720DT2630: 250 Hour ServiceFALSE
1075 - 2019-09-03107511060630:00.000:00.030Park brake slow to releaseTRUE
1075 - 2019-09-04107511061219:00.055:00.013296Park brake not releasingTRUE
1076 - 2019-06-16107610633255:00.025:00.030Air StartTRUE
1076 - 2019-06-20107610659330:00.002:00.0272Hydraulic Oil LeakTRUE

 

KPI_Target Table:

Fleet_IdAvailability_pctMean_Time_Btwn_FailureUtilisationMean_Time_First_Stop_PMMean_Time_To_Repair
850.915060001000.5
860.915060001000.5
870.915060001000.5
880.915060001000.5
890.915060001000.5
900.915060001000.5
910.915060001000.5
920.915060001000.5
930.915060001000.5
940.915060001000.5
950.915060001000.5
960.915060001000.5
970.915060001000.5
980.915060001000.5
990.915060001000.5
1000.915060001000.5
1010.915060001000.5
1020.915060001000.5
1030.915060001000.5
1040.915060001000.5
1050.915060001000.5
1060.915060001000.5
1070.915060001000.5
1080.915060001000.5
1090.915060001000.5
1100.915060001000.5
1110.915060001000.5
1120.915060001000.5

 

The DailyDowntime and Events tables are linked to the EquipmentDetails table by EqpDateKey. The KPI_Target table is linked to the EquipmentDetails table by FleetKey.

 

Please let me know if you need any more information.

Thanks for your help.

 

Regards,

Brandon

Frequent Visitor

Sorry, just reliased the ActualDownTime and ActualUpTime in the Events table was of the wrong format.

 

Try using this Events Table instead:

 

EqpDateKeyEqp_Plan_IDEvent_IDActual_Down_TimeActual_Up_TimeDownTimeDescriptionBreakdown
1074 - 2019-06-20107410714220-06-19 06:0020-06-19 06:000WT2066: Steering Components Inspection - NDT ( PO 52384 )FALSE
1074 - 2019-06-25107410683125-06-19 20:0026-06-19 03:00420WT2066: BLOWN A/C COMPRESSORTRUE
1074 - 2019-06-28107410681228-06-19 09:1528-06-19 09:4530WT2066 - FIRE SUPPRESSION INSPECTION - 6 MONTHLY (L1) Dont move FSI are bookedFALSE
1074 - 2019-07-14107410773014-07-19 21:0014-07-19 22:0060WT2066 - Sprays not workingTRUE
1074 - 2019-07-23107410505123-07-19 06:0024-07-19 04:001320WT2066: 500 HOUR SERVICE - KITFALSE
1074 - 2019-07-28107410843728-07-19 08:3028-07-19 12:30240WT2066 - Wont startTRUE
1074 - 2019-07-31107410852631-07-19 01:0031-07-19 01:000WT2066: C SAMPLE WHEEL HUB LEFTTRUE
1074 - 2019-08-16107410940116-08-19 07:0016-08-19 09:00120WT2066 Brake Test - Fully LoadedFALSE
1074 - 2019-08-16107410505616-08-19 18:0017-08-19 06:00720WT2066: 250 HOUR SERVICE /FALSE
1074 - 2019-09-03107411051803-09-19 08:0003-09-19 08:000WT2066: Site Compliance Inspection (FRC)FALSE
1074 - 2019-09-05107410956805-09-19 18:0008-09-19 00:003240WT2066: 2000 HOUR SERVICE - KIT/ Hoses 24 are booked in for the 06/09/2019 6am start.FALSE
1074 - 2019-09-09107411093709-09-19 18:0010-09-19 00:00360WT2066 Horn not workingTRUE
1074 - 2019-09-12107411107712-09-19 10:1812-09-19 10:180WT2066 Steering Components Inspection - NDT PO 58905FALSE
1075 - 2019-06-18107510646018-06-19 07:4518-06-19 20:22757Coolant C SampleTRUE
1075 - 2019-06-20107510656220-06-19 07:0020-06-19 07:3030Low coolantTRUE
1075 - 2019-06-29107510698829-06-19 13:0029-06-19 14:3090Aircon fan U/STRUE
1075 - 2019-07-02107510654702-07-19 06:0003-07-19 13:201880DT2630: 250 Hour ServiceFALSE
1075 - 2019-07-17107510784217-07-19 06:0017-07-19 11:15315Fire suppression faultFALSE
1075 - 2019-07-17107510790117-07-19 16:0017-07-19 16:2020Air startFALSE
1075 - 2019-07-18107510791618-07-19 07:4518-07-19 10:00135Lost cab powerFALSE
1075 - 2019-07-19107510795119-07-19 08:2019-07-19 08:3515Start faultFALSE
1075 - 2019-07-26107510652726-07-19 07:1027-07-19 04:301280DT2630: 2000 Hour ServiceFALSE
1075 - 2019-08-08107510909408-08-19 18:4508-08-19 19:1530Air StartTRUE
1075 - 2019-08-13107510933213-08-19 06:5013-08-19 07:3040Door Alarm Staying onTRUE
1075 - 2019-08-19107510969119-08-19 19:0019-08-19 19:5555TC Filter PluggedTRUE
1075 - 2019-08-21107510866621-08-19 06:0021-08-19 18:00720DT2630: 250 Hour ServiceFALSE
1075 - 2019-09-03107511060603-09-19 19:3003-09-19 20:0030Park brake slow to releaseTRUE
1075 - 2019-09-04107511061204-09-19 04:1913-09-19 09:5513296Park brake not releasingTRUE
1076 - 2019-06-16107610633216-06-19 06:5516-06-19 07:2530Air StartTRUE
1076 - 2019-06-20107610659320-06-19 17:3020-06-19 22:02272Hydraulic Oil LeakTRUE
1076 - 2019-06-20107610651220-06-19 00:2020-06-19 00:5030Air StartTRUE
1076 - 2019-06-20107610511720-06-19 08:0020-06-19 15:45465DT2530 - Fire Suppression Service/Inspection L2FALSE
1076 - 2019-06-23107610559023-06-19 06:0023-06-19 17:30690DT2530: DT2530 - 1000 Hour ServiceFALSE
1076 - 2019-06-28107610695428-06-19 07:1528-06-19 10:10175No startTRUE
1076 - 2019-06-30107610701430-06-19 13:5530-06-19 14:2530Air tap leakingTRUE

@Bquinn 

 

Many don't manage to provide sample data, so I thank you for that Smiley Very Happy

 

I have built up a sample file, but I have the problem that the relationships don't seem to work properly.

 

I also believe that your relationships are the cause of the misbehaviour. (For example, is the filter direction properly defined in your model?)

 

So to get to the bottom of these errors, I need the Power BI file from you.

 

In this forum the file must be available as Link to an Ondrive/Google Drive whatever.

 

BR,

Josef

@JosefPrakljacic 

 

I've set up a DropBox but need your email address to share the file with you. Are you able to provide this?

@Bquinn It seems that we have a bug here or the percentage is calculated in a way I don't understand.

 

For the others what have I done - I changed the formatting rules and used numbers instead of percentages (0, 0.9, 1).

It worked then...

 

BR,

Josef

View solution in original post

@JosefPrakljacic 

 

Now that we have the color conditional formatting working based on a fixed value, is it possible to use a variable instead? Namely, the Avialability and MTBF KPI targets? 

Ideal result would be to have the column chart be green if it is above these variable targets and red if it is below the target.

 

Regards,

Brandon

I think it is possible. You write a measure which is generating the color.

May I refer you to this awesome posthttps://radacad.com/dax-and-conditional-formatting-better-together-find-the-biggest-and-smallest-num...

 

BR,

Josef

Hi Josef,
 
I've come up with the measure:
 
Availability Colour = IF(
                                     (   (SUM(EquipmentDetails[Scheduled_Hours]) - SUM(DailyDowntime[DailyDownTimeHrs])  )/                     SUM(EquipmentDetails[Scheduled_Hours])   )
                                                                              >= AVERAGE(TargetKPIs[Availability_pct]), "01B8AA", "#FD625E")
 
And then format the color based on "field value" of this measure.
 
The only problem is that I'm using the Average of the Target Availability and not individual values for each equipment. This becomes an issue if I filter the charts to include two or more different models/assets that do not have the same availability target.
 
Can you think of a solution?
 
Regards,
Brandon

Hallo @Bquinn,

 

so is it allowed to filter the availability with certain values or not - depending on your answer it would be either a heavy usage of hasonefilter or allselected. 

 

May I ask you to explain it a bit more with examples

 

BR,

Josef

 

https://dax.guide/hasonefilter/

https://dax.guide/allselected/

Hi @JosefPrakljacic 

 

An example might be the Availability Target for the equipment under model 793F may have a different Availability Target (for example 90%) than the equipment under model 830E-AC (for example 80%). Therefore, if I show both of these models in the chart at the same time, the color conditioning formatting will work on the average of their avaialbility (for example, if there is the same number of equipment for each model then the average Availability Target is 85% and so the color conditioning formatting will make the bars green if they're above 85% and red if they're below this). Instead, I'm trying to find a way to make the bars green if they're above 90% for the 793F equipment and green if they're above 85% for the 830E-AC equipment.

 

Regards,

Brandon 

Legend! 

 

Such a simple fix... the data type for the measure was % and still is % when it works, but as you said, it only seems to color format correctly with the rule based on number rather than %.

 

Thanks for your help.

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors