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

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
GRN_VPR8
Frequent Visitor

Average

I'm hoping someone can assist, but I seem to be lost. I have been looking at AVERAGE & AVERAGEX commands and various other posts and just seem to be able to get the desired result.

The challenge I have is a table similar to the one below. 

STATION  DATE DURATION 
STATION 1 21/09/23 0.10 
STATION 1 21/09/23 0.12 
STATION 1  22/09/23 0.15 
STATION 1 22/09/23 1.00 
STATION 2 22/09/23 0.09 
STATION - 2  22/09/23   
STATION 2  21/09/23 1.2 


What I would like to do is, In a table visual, have a summary like the one below with an average column.

 21/09/23 22/09/23 AVG
STATION 10.22 1.15 0.68
STATION 21.2 0.09 0.65

 

1 ACCEPTED SOLUTION

@GRN_VPR8 

Can you compare the attached PBIX against your version?

 

The measure values in a matrix appear to match the summary table in your first post, where each individual date shows a sum, and those sums are averaged in the AVG column.

 

I made a slight adjustment in the attached PBIX to make use of a Date table, with some alternatives shown commented:

Average of Daily Sum of Duration = 
AVERAGEX (
    VALUES ( 'Date'[Date] ),
    -- Alternatives:
    -- SUMMARIZE ( Data, 'Date'[Date] ),
    -- VALUES ( Data[DATE] ),
    CALCULATE ( SUM ( Data[DURATION] ) )
)

 

OwenAuger_0-1695495555168.png

Regards


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

4 REPLIES 4
OwenAuger
Super User
Super User

Hi @GRN_VPR8 

It appears you want the average of the daily sum of duration.

Try something like this as a starting point:

Average of Daily Sum of Duration = 
AVERAGEX (
    VALUES ( Data[DATE] ),
    CALCULATE ( SUM ( Data[DURATION] ) )
)

Using this measure in a Matrix like the example you posted, with STATION on Rows and DATE on Columns, the "Total" column would show the average of the daily totals. You can rename "Total" to "Average" in a Matrix visual as well if you like.

 

There may be some tweaking required depending how you would like to handle dates where the sum is blank. The above measure would exclude blanks from the average calculation.

 

Regards


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Thanks for replying. I tried that, and all I got was an identical sum of duration for the day, not the average I was looking for.

@GRN_VPR8 

Can you compare the attached PBIX against your version?

 

The measure values in a matrix appear to match the summary table in your first post, where each individual date shows a sum, and those sums are averaged in the AVG column.

 

I made a slight adjustment in the attached PBIX to make use of a Date table, with some alternatives shown commented:

Average of Daily Sum of Duration = 
AVERAGEX (
    VALUES ( 'Date'[Date] ),
    -- Alternatives:
    -- SUMMARIZE ( Data, 'Date'[Date] ),
    -- VALUES ( Data[DATE] ),
    CALCULATE ( SUM ( Data[DURATION] ) )
)

 

OwenAuger_0-1695495555168.png

Regards


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Thank you for figuring it out!

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.