cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
jader3rd Regular Visitor
Regular Visitor

Calculate the average of a sums of a subset of values per day

I have a dataset where the series column has 8 possible value SeriesA_1, SeriesA_2, SeriesA_3, SeriesA_4, SeriesB_1, SeriesB_2, SeriesB_3, SeriesB_4. Any one of them may or may not show up in every day.

What I want is the average of the sum per day of a subset of the series.

I can get the sum of a subset by using:

SUMX(FILTER(Query1, Query1[Series] IN {"SeriesA_1", "SeriesA_2", "SeriesA_3", "SeriesA_4"}), Query1[Value])

But now I need a way to appy a filter, such that it will only be doing the summing on a per day basis, and then take the average of those values.

I can't just wrap an Average around it, because Average expects a column name, and there is no column name.

AverageX is expecting a table and an expression. I was hoping that if I made a table of dates, it would then take the average of each expression per date:

AVERAGEX(FILTER(Query1, DISTINCT(Query1[Date])), SUMX(FILTER(Query1, Query1[Series] IN {"SeriesA_1", "SeriesA_2", "SeriesA_3", "SeriesA_4"}), Query1[Value]))

But that didn't work. The error is A table of multiple values was supplied where a single value was expected.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Calculate the average of a sums of a subset of values per day

Hi @jader3rd

To avoid this error "A table of multiple values ...", modify with following mearure

Measure 2 = AVERAGEX(DISTINCT(Query1[Date]),SUMX(FILTER(Query1, Query1[Series] 

IN {"SeriesA_1", "SeriesA_2", "SeriesA_3", "SeriesA_4"}), Query1[Value]))

 

Based on my understanding, your requirement is to calculate

average of the sum per day of a subset of the series

for example

Series                DATE      value      average

SeriesA_1          day1       2            (2+4)/2

SeriesA_1          day2       4            (2+4)/2

SeriesA_2          day1       4            (4+4+4)/3

SeriesA_2          day2       4            (4+4+4)/3

SeriesA_2          day3       4            (4+4+4)/3

Right?

 

If so, please follow my advice

Measure = LEFT(MAX([series]),7)

Measure 3 = SUMX(FILTER(ALLEXCEPT(Query1,Query1[series]),[Measure]="SeriesA"),[Value])

Measure 4 = CALCULATE(DISTINCTCOUNT(Query1[date]),ALLEXCEPT(Query1,Query1[series]))

Measure 5 = [Measure 3]/[Measure 4]

13.png

 

Best Regards

Maggie

     

View solution in original post

3 REPLIES 3
Community Support Team
Community Support Team

Re: Calculate the average of a sums of a subset of values per day

Hi @jader3rd

To avoid this error "A table of multiple values ...", modify with following mearure

Measure 2 = AVERAGEX(DISTINCT(Query1[Date]),SUMX(FILTER(Query1, Query1[Series] 

IN {"SeriesA_1", "SeriesA_2", "SeriesA_3", "SeriesA_4"}), Query1[Value]))

 

Based on my understanding, your requirement is to calculate

average of the sum per day of a subset of the series

for example

Series                DATE      value      average

SeriesA_1          day1       2            (2+4)/2

SeriesA_1          day2       4            (2+4)/2

SeriesA_2          day1       4            (4+4+4)/3

SeriesA_2          day2       4            (4+4+4)/3

SeriesA_2          day3       4            (4+4+4)/3

Right?

 

If so, please follow my advice

Measure = LEFT(MAX([series]),7)

Measure 3 = SUMX(FILTER(ALLEXCEPT(Query1,Query1[series]),[Measure]="SeriesA"),[Value])

Measure 4 = CALCULATE(DISTINCTCOUNT(Query1[date]),ALLEXCEPT(Query1,Query1[series]))

Measure 5 = [Measure 3]/[Measure 4]

13.png

 

Best Regards

Maggie

     

View solution in original post

jader3rd Regular Visitor
Regular Visitor

Re: Calculate the average of a sums of a subset of values per day

Why is it DistinctCount instead of Distinct?

Community Support Team
Community Support Team

Re: Calculate the average of a sums of a subset of values per day

Hi @jader3rd

The DISTINCTCOUNT function counts the number of distinct values in a column.

DISTINCT(<column>)  Returns a one-column table that contains the distinct values from the specified column.

 

Best Regards

Maggie

 

 

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 200 members 1,806 guests
Please welcome our newest community members: