Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Yeroc
Frequent Visitor

Summarizing and Averaging

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. 

summarize_quesiton.png

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

 

 

1 ACCEPTED SOLUTION
v-yuta-msft
Community Support
Community Support

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]))))

Capture.PNG 

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

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

2.PNG 

 

Regards,

Jimmy Tao

View solution in original post

1 REPLY 1
v-yuta-msft
Community Support
Community Support

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]))))

Capture.PNG 

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

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

2.PNG 

 

Regards,

Jimmy Tao

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.