statisticspowerbidaxpowerpivotmeasures

weighted average by category filtered by time period

I've got a headache which I would love some help with

So I have the following table:

Table 1:

Date         Hour   Volume  Value   Price      
10/09/2018   1      10      400     40.0
10/09/2018   2      80      200     2.5
10/09/2018   3      14      190     13.6
10/09/2018   4      74      140     1.9
11/09/2018   1      34      547     16.1
11/09/2018   2      26      849     32.7
11/09/2018   3      95      279     2.9
11/09/2018   4      31      216     7.0

Then what I wan to do is view the weighted average by hour.

e.g.

Hour    Price
1       21.52272727
2       9.896226415
3       4.302752294
4       3.39047619

And if possible (bonus point). Then be able to change this by time period e.g. each hour within specified dates.

The way it looks in Excel:

    A            B      C       D       E
1   Date         Hour   Volume  Value   Price      
2   10/09/2018   1      10      400     40.0
3   10/09/2018   2      80      200     2.5
4   10/09/2018   3      14      190     13.6
5   10/09/2018   4      74      140     1.9
6   11/09/2018   1      34      547     16.1
7   11/09/2018   2      26      849     32.7
8   11/09/2018   3      95      279     2.9
9   11/09/2018   4      31      216     7.0

The output should be:

Hour    Price
1       21.52272727
2       9.896226415
3       4.302752294
4       3.39047619

Calculated Like:

Hour    Price
1       ((E2*C2)+(E6*C6))/SUM(C2,C6)
2       ((E3*C3)+(E7*C7))/SUM(C3,C7)
3       ((E4*C4)+(E8*C8))/SUM(C4,C8)
4       ((E5*C5)+(E9*C9))/SUM(C5,C9)

I've looked at lots of weighted average questions and answers and they all make sense but I can't quite put them together the way I want.

I hope that makes sense.

Thanks guys,

Solution

  • I have reproduced your desired report:

    enter image description here

    There is no need to involve price in the calculations. Weighted Average price is simply total value / total volume, for the selected set of dates. Let's say your table is called "Data". Create measure:

    Weighted Average Price = DIVIDE( SUM(Data[Value]), SUM(Data[Volume]))

    Put it into a pivot table against hours, and you are done.

    The formular will work correctly for any set of dates you select. For example, in a version of the above report with both hours and dates on pivot:

    enter image description here

    you can see that it calculates prices correctly on individual dates, subtotals and the total.