# PowerPivot formula for row wise weighted average

• ### Question

• Hi everybody,

I have a table in `PowerPivot` which contains the logged data of a traffic control camera mounted on a road. This table is filled the velocity and the number of vehicles that pass this camera during a specific time(e.g. 14:10 - 15:25). Now I want to know that how can I get the average velocity of cars for an specific hour and list them in a separate table with 24 rows(hour 0 - 23) where the second column of each row is the weighted average velocity of that hour? A sample of my stat_table data is given below:

count    vel          hour
-----    --------   ----
133    96.00237    15
117    91.45705    21
81      81.90521    6
2        84.29946    21
4        77.7841      18
1        140.8766    17
2        56.14951    14
6        71.72839    13
4        64.14309    9
1        60.949        17
1        77.00728    21
133    100.3956    6
109    100.8567    15
54      86.6369      9
1        83.96901    17
10      114.6556    21
6        85.39127    18
1        76.77993    15
3        113.3561    2
3        94.48055    2

In a separate `PowerPivot` table I have 24 rows and 2 columns(column1 is for hours and column 2 for weighted averages) and when I enter my formula, the whole rows get updated with the same number. My formula is:

`=sumX(FILTER(stat_table, stat_table[hour]=[hour]), stat_table[count] * stat_table[vel])/sumX(FILTER(stat_table, stat_table[hour]=[hour]), stat_table[count])`

I have also asked this question at Stackoverflow.

Wednesday, April 22, 2015 6:39 AM

• Hi Reza,

According to your description, you need to calculated the average value of Vel group on Count and Hour, right?

I have tested it on my local environment, the sample expression below is for you reference.
=CALCULATE(sum(Sample[Vel]),ALLEXCEPT(Sample,Sample[Count],Sample[Hour]))

If this is not what you want, please elaborate the issue, so that we can make further analysis.

Regards,

Charlie Liao
TechNet Community Support

• Proposed as answer by Monday, April 27, 2015 9:32 AM
• Marked as answer by Friday, May 8, 2015 10:43 AM
Thursday, April 23, 2015 7:19 AM
• Hi Reza,

According to your description, you need to calculated the average value of Vel group on Count and Hour, right?

I have tested it on my local environment, the sample expression below is for you reference.
=CALCULATE(sum(Sample[Vel]),ALLEXCEPT(Sample,Sample[Count],Sample[Hour]))

If this is not what you want, please elaborate the issue, so that we can make further analysis.

Regards,

Charlie Liao
TechNet Community Support

Thank you,

Yes it works. But I decided to use a 'measure' rather than a calculated column. My measure's formula is:

`=SUMX(stat_table, [vel] * [count]) / SUMX(stat_table, [count])`

• Proposed as answer by Monday, April 27, 2015 9:32 AM
• Marked as answer by Friday, May 8, 2015 10:43 AM
Saturday, April 25, 2015 8:10 AM

### All replies

• Hi Reza,

According to your description, you need to calculated the average value of Vel group on Count and Hour, right?

I have tested it on my local environment, the sample expression below is for you reference.
=CALCULATE(sum(Sample[Vel]),ALLEXCEPT(Sample,Sample[Count],Sample[Hour]))

If this is not what you want, please elaborate the issue, so that we can make further analysis.

Regards,

Charlie Liao
TechNet Community Support

• Proposed as answer by Monday, April 27, 2015 9:32 AM
• Marked as answer by Friday, May 8, 2015 10:43 AM
Thursday, April 23, 2015 7:19 AM
• Hi Reza,

According to your description, you need to calculated the average value of Vel group on Count and Hour, right?

I have tested it on my local environment, the sample expression below is for you reference.
=CALCULATE(sum(Sample[Vel]),ALLEXCEPT(Sample,Sample[Count],Sample[Hour]))

If this is not what you want, please elaborate the issue, so that we can make further analysis.

Regards,

Charlie Liao
TechNet Community Support

Thank you,

Yes it works. But I decided to use a 'measure' rather than a calculated column. My measure's formula is:

`=SUMX(stat_table, [vel] * [count]) / SUMX(stat_table, [count])`

• Proposed as answer by Monday, April 27, 2015 9:32 AM
• Marked as answer by Friday, May 8, 2015 10:43 AM
Saturday, April 25, 2015 8:10 AM