Group Time like on excel

[natabird3]

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: 

MaxValue = MAXX ( SUMMARIZE ( 'Content', 'Content'[Data], 'Content'[Weekday] , "MaxCol", [Data] ), [MaxCol] )

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

[v-juanli-msft · Accepted solution]

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
Maggie

Community 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.

[natabird3]

Actually i was able to get it working another way, thanks for the help anyways 🙂