locked
PowerPivot formula for row wise weighted average RRS feed

  • 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

Answers

  • 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 Michael Amadi Monday, April 27, 2015 9:32 AM
    • Marked as answer by Michael Amadi 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 Michael Amadi Monday, April 27, 2015 9:32 AM
    • Marked as answer by Michael Amadi 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 Michael Amadi Monday, April 27, 2015 9:32 AM
    • Marked as answer by Michael Amadi 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 Michael Amadi Monday, April 27, 2015 9:32 AM
    • Marked as answer by Michael Amadi Friday, May 8, 2015 10:43 AM
    Saturday, April 25, 2015 8:10 AM