Forum Discussion

Anonymous

Not applicable

6 years ago

Solved

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% t...

Need Help

JosefPrakljacic
6 years ago
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

Anonymous

Not applicable

6 years ago

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

Anonymous

Not applicable

6 years ago

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

JosefPrakljacic
Solution Sage
6 years ago
Anonymous

Many don't manage to provide sample data, so I thank you for that :smileyvery-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
- Anonymous
  Not applicable
  6 years ago
  JosefPrakljacic
  
  I've set up a DropBox but need your email address to share the file with you. Are you able to provide this?
  - JosefPrakljacic
    Solution Sage
    6 years ago
    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

Forum Discussion

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

Related content

Conditional Formatting

Conditional Formatting using DAX

Conditional Formatting

Matrix Row Conditional Formatting

Condition formatting