我在 PowerPivot 中有一个表,其中包含安装在道路上的交通控制摄像机的记录数据。这张表填满了在特定时间内(例如14:10-15:25)通过该摄像头的速度和车辆数量。现在,我想知道如何获取特定小时的汽车平均速度,并将其在包含24行(小时0-23)的单独表格中列出,其中每行的第二列是该小时的加权平均速度?我的stat_table数据示例如下:
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在单独的 PowerPivot 表中,我有24行和2列,但是当我输入公式时,整个行都会更新具有相同的编号。我的公式是: = 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])
In a separate PowerPivot table I have 24 rows and 2 columns but 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])
推荐答案
创建一个新的计算列,名为 WeightedVelocity,如下所示
Create a new calculated column named "WeightedVelocity" as follows WeightedVelocity = [count]*[vel]
创建度量 WeightedAverage如下
Create a measure "WeightedAverage" as follows
WeightedAverage = sum(stat_table[WeightedVelocity]) / sum(stat_table[count])
在数据透视表的VALUES区域中使用度量 WeightedAverage,并在ROWS中使用小时列获取广告令人讨厌的结果。
Use measure "WeightedAverage" in VALUES area of pivot Table and use "hour" column in ROWS to get desired result.
更多推荐
逐行加权平均值的PowerPivot公式
发布评论