Forum Discussion
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.
Anonymous 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
12 Replies
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:
EqpDateKey Usage_Date Equipment Eqpdescription Site FleetKey Fleet EqpGroup Model Actual_Usage Scheduled_Hours 1074 - 2019-06-14 14-06-2019 00:00:00 WT2066 WATER TRUCK - CAT 777F (91t) - JRP03031 Saraji Rental 367 SB-Water Cart Water Truck 777F-WT 0.074303406 24 1074 - 2019-06-15 15-06-2019 00:00:00 WT2066 WATER TRUCK - CAT 777F (91t) - JRP03031 Saraji Rental 367 SB-Water Cart Water Truck 777F-WT 0.074303406 24 1074 - 2019-06-16 16-06-2019 00:00:00 WT2066 WATER TRUCK - CAT 777F (91t) - JRP03031 Saraji Rental 367 SB-Water Cart Water Truck 777F-WT 0.089226006 24 1074 - 2019-06-17 17-06-2019 00:00:00 WT2066 WATER TRUCK - CAT 777F (91t) - JRP03031 Saraji Rental 367 SB-Water Cart Water Truck 777F-WT 0.790588235 24 1074 - 2019-06-18 18-06-2019 00:00:00 WT2066 WATER TRUCK - CAT 777F (91t) - JRP03031 Saraji Rental 367 SB-Water Cart Water Truck 777F-WT 2.414389389 24 1074 - 2019-06-19 19-06-2019 00:00:00 WT2066 WATER TRUCK - CAT 777F (91t) - JRP03031 Saraji Rental 367 SB-Water Cart Water Truck 777F-WT 7.285792851 24 1074 - 2019-06-20 20-06-2019 00:00:00 WT2066 WATER TRUCK - CAT 777F (91t) - JRP03031 Saraji Rental 367 SB-Water Cart Water Truck 777F-WT 7.285792851 24 1074 - 2019-06-21 21-06-2019 00:00:00 WT2066 WATER TRUCK - CAT 777F (91t) - JRP03031 Saraji Rental 367 SB-Water Cart Water Truck 777F-WT 7.285792851 24 1074 - 2019-06-22 22-06-2019 00:00:00 WT2066 WATER TRUCK - CAT 777F (91t) - JRP03031 Saraji Rental 367 SB-Water Cart Water Truck 777F-WT 4.33082416 24 1074 - 2019-06-23 23-06-2019 00:00:00 WT2066 WATER TRUCK - CAT 777F (91t) - JRP03031 Saraji Rental 367 SB-Water Cart Water Truck 777F-WT 0.887063655 24 1074 - 2019-06-24 24-06-2019 00:00:00 WT2066 WATER TRUCK - CAT 777F (91t) - JRP03031 Saraji Rental 367 SB-Water Cart Water Truck 777F-WT 5.430808357 24 1074 - 2019-06-25 25-06-2019 00:00:00 WT2066 WATER TRUCK - CAT 777F (91t) - JRP03031 Saraji Rental 367 SB-Water Cart Water Truck 777F-WT 6.781651376 24 1074 - 2019-06-26 26-06-2019 00:00:00 WT2066 WATER TRUCK - CAT 777F (91t) - JRP03031 Saraji Rental 367 SB-Water Cart Water Truck 777F-WT 6.781651376 24 1074 - 2019-06-27 27-06-2019 00:00:00 WT2066 WATER TRUCK - CAT 777F (91t) - JRP03031 Saraji Rental 367 SB-Water Cart Water Truck 777F-WT 6.781651376 24 1074 - 2019-06-28 28-06-2019 00:00:00 WT2066 WATER TRUCK - CAT 777F (91t) - JRP03031 Saraji Rental 367 SB-Water Cart Water Truck 777F-WT 6.781651376 24 1074 - 2019-06-29 29-06-2019 00:00:00 WT2066 WATER TRUCK - CAT 777F (91t) - JRP03031 Saraji Rental 367 SB-Water Cart Water Truck 777F-WT 6.781651376 24 1074 - 2019-06-30 30-06-2019 00:00:00 WT2066 WATER TRUCK - CAT 777F (91t) - JRP03031 Saraji Rental 367 SB-Water Cart Water Truck 777F-WT 6.781651376 24 1074 - 2019-07-01 01-07-2019 00:00:00 WT2066 WATER TRUCK - CAT 777F (91t) - JRP03031 Saraji Rental 367 SB-Water Cart Water Truck 777F-WT 0.389235474 24 1074 - 2019-07-02 02-07-2019 00:00:00 WT2066 WATER TRUCK - CAT 777F (91t) - JRP03031 Saraji Rental 367 SB-Water Cart Water Truck 777F-WT 0.111304348 24 1074 - 2019-07-03 03-07-2019 00:00:00 WT2066 WATER TRUCK - CAT 777F (91t) - JRP03031 Saraji Rental 367 SB-Water Cart Water Truck 777F-WT 0.111304348 24 1074 - 2019-07-04 04-07-2019 00:00:00 WT2066 WATER TRUCK - CAT 777F (91t) - JRP03031 Saraji Rental 367 SB-Water Cart Water Truck 777F-WT 0.111304348 24 1074 - 2019-07-05 05-07-2019 00:00:00 WT2066 WATER TRUCK - CAT 777F (91t) - JRP03031 Saraji Rental 367 SB-Water Cart Water Truck 777F-WT 0.111304348 24 1074 - 2019-07-06 06-07-2019 00:00:00 WT2066 WATER TRUCK - CAT 777F (91t) - JRP03031 Saraji Rental 367 SB-Water Cart Water Truck 777F-WT 0.111304348 24 DailyDowntime Table:
EqpDateKey EquipmentKey DownHrs DailyDownTimeHrs 1074 - 2019-06-25 1074 4 4 1074 - 2019-06-26 1074 3 3 1074 - 2019-06-28 1074 0.5 0.5 1074 - 2019-07-14 1074 1 1 1074 - 2019-07-23 1074 18 18 1074 - 2019-07-24 1074 4 4 1074 - 2019-07-28 1074 4 4 1074 - 2019-08-16 1074 8 8 1074 - 2019-08-17 1074 6 6 1074 - 2019-09-05 1074 6 6 1074 - 2019-09-06 1074 24 24 1074 - 2019-09-07 1074 24 24 1074 - 2019-09-08 1074 0 0 1074 - 2019-09-09 1074 6 6 1074 - 2019-09-10 1074 0 0 1075 - 2019-06-18 1075 12.61666679 12.61666679 1075 - 2019-06-20 1075 0.5 0.5 1075 - 2019-06-29 1075 1.5 1.5 1075 - 2019-07-02 1075 17.99999982 17.99999982 1075 - 2019-07-03 1075 13.3333332 13.3333332 1075 - 2019-07-17 1075 5.583333343 5.583333343 1075 - 2019-07-18 1075 2.25 2.25 1075 - 2019-07-19 1075 0.25 0.25 1075 - 2019-07-26 1075 16.83333384 16.83333384 1075 - 2019-07-27 1075 4.500000134 4.500000134 1075 - 2019-08-08 1075 0.5 0.5 1075 - 2019-08-13 1075 0.666666687 0.666666687 Events/Breakdowns Table:
EqpDateKey Eqp_Plan_ID Event_ID Actual_Down_Time Actual_Up_Time DownTime Description Breakdown 1074 - 2019-06-20 1074 107142 00:00.0 00:00.0 0 WT2066: Steering Components Inspection - NDT ( PO 52384 ) FALSE 1074 - 2019-06-25 1074 106831 00:00.0 00:00.0 420 WT2066: BLOWN A/C COMPRESSOR TRUE 1074 - 2019-06-28 1074 106812 15:00.0 45:00.0 30 WT2066 - FIRE SUPPRESSION INSPECTION - 6 MONTHLY (L1) Dont move FSI are booked FALSE 1074 - 2019-07-14 1074 107730 00:00.0 00:00.0 60 WT2066 - Sprays not working TRUE 1074 - 2019-07-23 1074 105051 00:00.0 00:00.0 1320 WT2066: 500 HOUR SERVICE - KIT FALSE 1074 - 2019-07-28 1074 108437 30:00.0 30:00.0 240 WT2066 - Wont start TRUE 1074 - 2019-07-31 1074 108526 00:00.0 00:00.0 0 WT2066: C SAMPLE WHEEL HUB LEFT TRUE 1074 - 2019-08-16 1074 109401 00:00.0 00:00.0 120 WT2066 Brake Test - Fully Loaded FALSE 1074 - 2019-08-16 1074 105056 00:00.0 00:00.0 720 WT2066: 250 HOUR SERVICE / FALSE 1074 - 2019-09-03 1074 110518 00:00.0 00:00.0 0 WT2066: Site Compliance Inspection (FRC) FALSE 1074 - 2019-09-05 1074 109568 00:00.0 00:00.0 3240 WT2066: 2000 HOUR SERVICE - KIT/ Hoses 24 are booked in for the 06/09/2019 6am start. FALSE 1074 - 2019-09-09 1074 110937 00:00.0 00:00.0 360 WT2066 Horn not working TRUE 1074 - 2019-09-12 1074 111077 18:00.0 18:00.0 0 WT2066 Steering Components Inspection - NDT PO 58905 FALSE 1075 - 2019-06-18 1075 106460 45:00.0 22:00.0 757 Coolant C Sample TRUE 1075 - 2019-06-20 1075 106562 00:00.0 30:00.0 30 Low coolant TRUE 1075 - 2019-06-29 1075 106988 00:00.0 30:00.0 90 Aircon fan U/S TRUE 1075 - 2019-07-02 1075 106547 00:00.0 20:00.0 1880 DT2630: 250 Hour Service FALSE 1075 - 2019-07-17 1075 107842 00:00.0 15:00.0 315 Fire suppression fault FALSE 1075 - 2019-07-17 1075 107901 00:00.0 20:00.0 20 Air start FALSE 1075 - 2019-07-18 1075 107916 45:00.0 00:00.0 135 Lost cab power FALSE 1075 - 2019-07-19 1075 107951 20:00.0 35:00.0 15 Start fault FALSE 1075 - 2019-07-26 1075 106527 10:00.0 30:00.0 1280 DT2630: 2000 Hour Service FALSE 1075 - 2019-08-08 1075 109094 45:00.0 15:00.0 30 Air Start TRUE 1075 - 2019-08-13 1075 109332 50:00.0 30:00.0 40 Door Alarm Staying on TRUE 1075 - 2019-08-19 1075 109691 00:00.0 55:00.0 55 TC Filter Plugged TRUE 1075 - 2019-08-21 1075 108666 00:00.0 00:00.0 720 DT2630: 250 Hour Service FALSE 1075 - 2019-09-03 1075 110606 30:00.0 00:00.0 30 Park brake slow to release TRUE 1075 - 2019-09-04 1075 110612 19:00.0 55:00.0 13296 Park brake not releasing TRUE 1076 - 2019-06-16 1076 106332 55:00.0 25:00.0 30 Air Start TRUE 1076 - 2019-06-20 1076 106593 30:00.0 02:00.0 272 Hydraulic Oil Leak TRUE KPI_Target Table:
Fleet_Id Availability_pct Mean_Time_Btwn_Failure Utilisation Mean_Time_First_Stop_PM Mean_Time_To_Repair 85 0.9 150 6000 100 0.5 86 0.9 150 6000 100 0.5 87 0.9 150 6000 100 0.5 88 0.9 150 6000 100 0.5 89 0.9 150 6000 100 0.5 90 0.9 150 6000 100 0.5 91 0.9 150 6000 100 0.5 92 0.9 150 6000 100 0.5 93 0.9 150 6000 100 0.5 94 0.9 150 6000 100 0.5 95 0.9 150 6000 100 0.5 96 0.9 150 6000 100 0.5 97 0.9 150 6000 100 0.5 98 0.9 150 6000 100 0.5 99 0.9 150 6000 100 0.5 100 0.9 150 6000 100 0.5 101 0.9 150 6000 100 0.5 102 0.9 150 6000 100 0.5 103 0.9 150 6000 100 0.5 104 0.9 150 6000 100 0.5 105 0.9 150 6000 100 0.5 106 0.9 150 6000 100 0.5 107 0.9 150 6000 100 0.5 108 0.9 150 6000 100 0.5 109 0.9 150 6000 100 0.5 110 0.9 150 6000 100 0.5 111 0.9 150 6000 100 0.5 112 0.9 150 6000 100 0.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
Sorry, just reliased the ActualDownTime and ActualUpTime in the Events table was of the wrong format.
Try using this Events Table instead:
EqpDateKey Eqp_Plan_ID Event_ID Actual_Down_Time Actual_Up_Time DownTime Description Breakdown 1074 - 2019-06-20 1074 107142 20-06-19 06:00 20-06-19 06:00 0 WT2066: Steering Components Inspection - NDT ( PO 52384 ) FALSE 1074 - 2019-06-25 1074 106831 25-06-19 20:00 26-06-19 03:00 420 WT2066: BLOWN A/C COMPRESSOR TRUE 1074 - 2019-06-28 1074 106812 28-06-19 09:15 28-06-19 09:45 30 WT2066 - FIRE SUPPRESSION INSPECTION - 6 MONTHLY (L1) Dont move FSI are booked FALSE 1074 - 2019-07-14 1074 107730 14-07-19 21:00 14-07-19 22:00 60 WT2066 - Sprays not working TRUE 1074 - 2019-07-23 1074 105051 23-07-19 06:00 24-07-19 04:00 1320 WT2066: 500 HOUR SERVICE - KIT FALSE 1074 - 2019-07-28 1074 108437 28-07-19 08:30 28-07-19 12:30 240 WT2066 - Wont start TRUE 1074 - 2019-07-31 1074 108526 31-07-19 01:00 31-07-19 01:00 0 WT2066: C SAMPLE WHEEL HUB LEFT TRUE 1074 - 2019-08-16 1074 109401 16-08-19 07:00 16-08-19 09:00 120 WT2066 Brake Test - Fully Loaded FALSE 1074 - 2019-08-16 1074 105056 16-08-19 18:00 17-08-19 06:00 720 WT2066: 250 HOUR SERVICE / FALSE 1074 - 2019-09-03 1074 110518 03-09-19 08:00 03-09-19 08:00 0 WT2066: Site Compliance Inspection (FRC) FALSE 1074 - 2019-09-05 1074 109568 05-09-19 18:00 08-09-19 00:00 3240 WT2066: 2000 HOUR SERVICE - KIT/ Hoses 24 are booked in for the 06/09/2019 6am start. FALSE 1074 - 2019-09-09 1074 110937 09-09-19 18:00 10-09-19 00:00 360 WT2066 Horn not working TRUE 1074 - 2019-09-12 1074 111077 12-09-19 10:18 12-09-19 10:18 0 WT2066 Steering Components Inspection - NDT PO 58905 FALSE 1075 - 2019-06-18 1075 106460 18-06-19 07:45 18-06-19 20:22 757 Coolant C Sample TRUE 1075 - 2019-06-20 1075 106562 20-06-19 07:00 20-06-19 07:30 30 Low coolant TRUE 1075 - 2019-06-29 1075 106988 29-06-19 13:00 29-06-19 14:30 90 Aircon fan U/S TRUE 1075 - 2019-07-02 1075 106547 02-07-19 06:00 03-07-19 13:20 1880 DT2630: 250 Hour Service FALSE 1075 - 2019-07-17 1075 107842 17-07-19 06:00 17-07-19 11:15 315 Fire suppression fault FALSE 1075 - 2019-07-17 1075 107901 17-07-19 16:00 17-07-19 16:20 20 Air start FALSE 1075 - 2019-07-18 1075 107916 18-07-19 07:45 18-07-19 10:00 135 Lost cab power FALSE 1075 - 2019-07-19 1075 107951 19-07-19 08:20 19-07-19 08:35 15 Start fault FALSE 1075 - 2019-07-26 1075 106527 26-07-19 07:10 27-07-19 04:30 1280 DT2630: 2000 Hour Service FALSE 1075 - 2019-08-08 1075 109094 08-08-19 18:45 08-08-19 19:15 30 Air Start TRUE 1075 - 2019-08-13 1075 109332 13-08-19 06:50 13-08-19 07:30 40 Door Alarm Staying on TRUE 1075 - 2019-08-19 1075 109691 19-08-19 19:00 19-08-19 19:55 55 TC Filter Plugged TRUE 1075 - 2019-08-21 1075 108666 21-08-19 06:00 21-08-19 18:00 720 DT2630: 250 Hour Service FALSE 1075 - 2019-09-03 1075 110606 03-09-19 19:30 03-09-19 20:00 30 Park brake slow to release TRUE 1075 - 2019-09-04 1075 110612 04-09-19 04:19 13-09-19 09:55 13296 Park brake not releasing TRUE 1076 - 2019-06-16 1076 106332 16-06-19 06:55 16-06-19 07:25 30 Air Start TRUE 1076 - 2019-06-20 1076 106593 20-06-19 17:30 20-06-19 22:02 272 Hydraulic Oil Leak TRUE 1076 - 2019-06-20 1076 106512 20-06-19 00:20 20-06-19 00:50 30 Air Start TRUE 1076 - 2019-06-20 1076 105117 20-06-19 08:00 20-06-19 15:45 465 DT2530 - Fire Suppression Service/Inspection L2 FALSE 1076 - 2019-06-23 1076 105590 23-06-19 06:00 23-06-19 17:30 690 DT2530: DT2530 - 1000 Hour Service FALSE 1076 - 2019-06-28 1076 106954 28-06-19 07:15 28-06-19 10:10 175 No start TRUE 1076 - 2019-06-30 1076 107014 30-06-19 13:55 30-06-19 14:25 30 Air tap leaking TRUE