Same hour different result

[gyeea84]

Hi, 

I have a table with following data

which showing pick time and order release time per route. In this example, route 21B115 was released at 10 and picked in hour 10,11,and 12. I would like to show a chart where it show "Hour" in X-axis and "Amount" in Y-axis. Since I have 2 different hour result (pick and order release), how to I do a calculate based on same hour with two different result. 

For example, what I have done in Excel, result should be look something like this. 

Can anyone help on this matter? Thanks. 

[Anonymous · Accepted solution]

Hi  @gyeea84 ,

Here are the steps you can follow：

1. Click "Enter data" to manually create a table with numbers 0-24

2. Create measure.

measure_order =
CALCULATE(SUM('Table'[QuantityAsDPack]),FILTER(ALL('Table'),'Table'[RouteNo]=MAX('Table'[RouteNo])&&HOUR('Table'[OrderRelelseTime])=MAX('X'[Hour])))

measure_Pick =
CALCULATE(SUM('Table'[QuantityAsDPack]),FILTER(ALL('Table'),'Table'[RouteNo]=MAX('Table'[RouteNo])&&HOUR('Table'[PickedTime])=MAX('X'[Hour])))

3. Select the Clusterd column chart, place [Hour] of the X table in Axis, and place [measure_order] and [measure_Pick] in Values.

4. Open Format – X axis – set Start=6, End=12.

5. Result:

Best Regards,

Liu Yang

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