Forum Discussion
Skilled SharerGroup Time like on excel
Hello,
I have the following problem:
| Weekday | Time | 6-8 am | 8-10 am | 10-12 am | 12-14 pm | 14-16 pm | 16-18 pm | 18-20 pm | 20-22 pm | 22-24 pm | Total |
| Monday | 9 | 2 | 1 | 2 | 14 | ||||||
| Tuesday | 2 | 1 | 3 | 6 | |||||||
| Wednesday | 4 | 1 | 5 | 10 | |||||||
| Thursday | 3 | 1 | 1 | 3 | 8 | 16 | |||||
| Friday | 4 | 4 | 8 | 16 | |||||||
| Saturday | 5 | 5 | 10 | ||||||||
| Sunday | 1 | 1 | 2 | 4 |
I want to combine all the data that i have in the above table format, and highlight the max values for each day of the week and time period. The max values per row I did using:
Unfortuantely, for me what is difficult is combining the times, as currently i have a lot of data entries which are like:
12:45 am, 10:12 pm, 13:46 am, etc. I have tried multiple suggestions from other posts (for example grouping and creating additional tables, adding parameter, etc.) but none i was able to make work for me, can anyone help please?
Thanks,
Jordan
Hi natabird3
Assume you have a table like
1. open edit queries,
add column-> add date->date only,
add column-> add time->time only,
Close&&Apply
2. create calculated columns
hour = HOUR([Time]) time group = SWITCH ( TRUE (), [hour] >= 6 && [hour] < 8, "6-8 am", [hour] >= 8 && [hour] < 10, "8-10 am", [hour] >= 10 && [hour] < 12, "10-12 am", [hour] >= 12 && [hour] < 14, "12-14 pm", [hour] >= 14 && [hour] < 16, "14-16 pm", [hour] >= 16 && [hour] < 18, "16-18 pm", [hour] >= 18 && [hour] < 20, "18-20 pm", [hour] >= 20 && [hour] < 22, "20-22 pm", [hour] >= 22 && [hour] < 24, "22-24 pm" ) year/month/week/weekday = YEAR ( Sheet6[only date] ) & "/" & MONTH ( Sheet6[only date] ) & "/" & WEEKNUM ( Sheet6[only date] ) & "/" & WEEKDAY ( Sheet6[only date], 2 )3. create measures
max data = CALCULATE(MAX(Sheet6[data]),FILTER(ALLSELECTED(Sheet6),Sheet6[year/month/week/weekday]=MAX(Sheet6[year/month/week/weekday]))) color flag = IF(SUM(Sheet6[data])=[max data],1,0)
4. create a calendar table, create a relationship based on "Calendar"[Date] and "Sheet6"[only date].
calendar = ADDCOLUMNS ( CALENDAR ( DATE ( 2018, 1, 1 ), DATE ( 2019, 12, 31 ) ), "year", YEAR ( [Date] ), "month", MONTH ( [Date] ), "week", WEEKNUM ( [Date], 2 ), "weekday", WEEKDAY ( [Date], 2 ), "weekdayname", FORMAT ( [Date], "dddd" ) )5. Add [weekdayname] in the row of the matrix,
Use [color flag] when configuring conditional formatting for the [Data] column.
Best Regards
MaggieCommunity Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
v-juanli-msftCommunity Support
Hi natabird3
Assume you have a table like
1. open edit queries,
add column-> add date->date only,
add column-> add time->time only,
Close&&Apply
2. create calculated columns
hour = HOUR([Time]) time group = SWITCH ( TRUE (), [hour] >= 6 && [hour] < 8, "6-8 am", [hour] >= 8 && [hour] < 10, "8-10 am", [hour] >= 10 && [hour] < 12, "10-12 am", [hour] >= 12 && [hour] < 14, "12-14 pm", [hour] >= 14 && [hour] < 16, "14-16 pm", [hour] >= 16 && [hour] < 18, "16-18 pm", [hour] >= 18 && [hour] < 20, "18-20 pm", [hour] >= 20 && [hour] < 22, "20-22 pm", [hour] >= 22 && [hour] < 24, "22-24 pm" ) year/month/week/weekday = YEAR ( Sheet6[only date] ) & "/" & MONTH ( Sheet6[only date] ) & "/" & WEEKNUM ( Sheet6[only date] ) & "/" & WEEKDAY ( Sheet6[only date], 2 )3. create measures
max data = CALCULATE(MAX(Sheet6[data]),FILTER(ALLSELECTED(Sheet6),Sheet6[year/month/week/weekday]=MAX(Sheet6[year/month/week/weekday]))) color flag = IF(SUM(Sheet6[data])=[max data],1,0)
4. create a calendar table, create a relationship based on "Calendar"[Date] and "Sheet6"[only date].
calendar = ADDCOLUMNS ( CALENDAR ( DATE ( 2018, 1, 1 ), DATE ( 2019, 12, 31 ) ), "year", YEAR ( [Date] ), "month", MONTH ( [Date] ), "week", WEEKNUM ( [Date], 2 ), "weekday", WEEKDAY ( [Date], 2 ), "weekdayname", FORMAT ( [Date], "dddd" ) )5. Add [weekdayname] in the row of the matrix,
Use [color flag] when configuring conditional formatting for the [Data] column.
Best Regards
MaggieCommunity Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
natabird3Actually i was able to get it working another way, thanks for the help anyways :)