cancel
Showing results for
Did you mean:
jader3rd 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

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

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?

```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]``` Best Regards

Maggie

3 REPLIES 3 Community Support Team

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

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?

```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]``` Best Regards

Maggie

Highlighted
jader3rd 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

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

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

Announcements #### 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. #### 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.  #### Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI. Top Kudoed Authors
Users Online
Currently online: 293 members 2,887 guests
Recent signins:
• keerthig21 • ZinanYang • CassieZ • MichaelWuJoerns • MurtazaF • charlyrosero • DBowie • jdemers2001 • pkudva • nexxus1990 • KellyLX • kelzeki • Sarah_Jolliffe 