Summarizing and Averaging

[Yeroc]

Hello,

I have some data that comes in at a log level. I need to aggregate that data at a station level in order to get a total number of hours per station per day. I then need to get an average of all stations per day. You can see below the raw data on the left and the summed data on the right. I cannot just take an average of the hours to get the results I need. For example, the average on the left vs the right is 8.74 vs 17.49 and POWER BI will display 8.74 everytime. 



I've tried a summary table which worked but we are now needing to to combine data sets so a unique lookup code is needed to use global slicers. When that lookup code is added, the summary table is, you guessed it, unsummarized. 

Any ideas?

I've added that same sample data to a ondrive folder here. 

[v-yuta-msft · Accepted solution]

Hi Yeroc,

To achieve your requirement, firstly create a calculate column using DAX below to achieve the total per day per station:

result = CALCULATE(SUM(Table1[Hours]), FILTER(Table1, Table1[Station] = EARLIER(Table1[Station]) && DAY(Table1[Date]) = DAY(EARLIER(Table1[Date]))))

 

Then create a measure to calculate the average of the total hours of these stations:

Average = AVERAGEX(VALUES(Table1[result]), Table1[result])

 

Regards,

Jimmy Tao