Time of Day grouping

[jcastr02]

I have a list of stores with respective interval (time) and Avg Volume of calls.  I am trying to acheive the columns on the right where we want to see how many of those intervals go over threshold (below example is 10).  Then see by grouping different hours of operations where the highest call volume is.  Example for this store, during the 3pm-10pm interval, there are 14 Potential 30 min. intervals and out of those, 10 intervals were over the threshold of 10 calls.   10/14= 71.4% so we would recommend that timeframe to support (since it's the max).  Any ideas on how I could achieve the below?

**NOTE the first 3 columns is what I have in Power BI - and trying to achieve the right.  I'm stumped how to get the grouping of potential time ranges and then see which timeframe is the busiest.

Store #	Interval	Avg Volume/Interval		# of Potential Intervals	# Intervals Over Threshold	% Intervals Over Threshold	Recommendation
80	8:00	0		8am - 10pm	28	17	60.7%
80	8:30	1		8am - 9pm	26	15	57.7%
80	9:00	2		8am - 3pm	14	7	50.0%
80	9:30	10		9am - 10pm	26	17	65.4%
80	10:00	2		9am - 1pm	8	5	62.5%
80	10:30	3		1pm - 5pm	8	5	62.5%
80	11:00	12		3pm - 10pm	14	10	71.4%	x
80	11:30	18		4pm - 7pm	6	2	33.3%
80	12:00	20		9am - 5pm	16	10	62.5%
80	12:30	16		11am - 7pm	16	11	68.8%
80	13:00	5		6pm - 9pm	6	3	50.0%
80	13:30	4		9am - 7pm	20	12	60.0%
80	14:00	18
80	14:30	19
80	15:00	20
80	15:30	22
80	16:00	9
80	16:30	11
80	17:00	11
80	17:30	10
80	18:00	2
80	18:30	8
80	19:00	9
80	19:30	10
80	20:00	22
80	20:30	15
80	21:00	16
80	21:30	17
80	22:00	18
80	22:30	2

[v-weiyan1-msft · Accepted solution]

Hi @jcastr02 ,

Based on your description,
Please try the following steps:
1.You can create a Calcualted table.

Table = DATATABLE("# of Potential Intervals",STRING,
                    "Start",STRING,
                    "End",STRING,
                    {{"8am - 10pm","8:00","21:30"},
                     {"8am - 9pm","8:00","20:30"},
                     {"8am - 3pm","8:00","14:30"},
                     {"9am - 10pm","9:00","21:30"},
                     {"9am - 1pm","9:00","12:30"},
                     {"1pm - 5pm","13:00","16:30"},
                     {"3pm - 10pm","15:00","21:30"},
                     {"4pm - 7pm","16:00","18:30"},
                     {"9am - 5pm","9:00","16:30"},
                     {"11am - 7pm","11:00","18:30"},
                     {"6pm - 9pm","18:00","20:30"},
                     {"9am - 7pm","9:00","18:30"}}
                    )

And set the data type of the Start column and End column to Time.

2.Use the following code to create Calculated Columns.

# Intervals Over Threshold = 
COUNTX (
    FILTER (
        'StoreTable',
        'StoreTable'[Interval] >= 'Table'[Start]
            && 'StoreTable'[Interval] <= 'Table'[End]
    ),
    'StoreTable'[Avg Volume/Interva]
)

% Intervals Over Threshold = 
COUNTX (
    FILTER (
        'StoreTable',
        'StoreTable'[Interval] >= 'Table'[Start]
            && 'StoreTable'[Interval] <= 'Table'[End]
            && 'StoreTable'[Avg Volume/Interva] >= 10
    ),
    'StoreTable'[Avg Volume/Interva]
)

Recommendation = 
DIVIDE (
    'Table'[% Intervals Over Threshold],
    'Table'[# Intervals Over Threshold]
)

Result is as below.

Best Regards,
Yulia Yan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.