cancel
Showing results for
Did you mean:
Frequent Visitor

## Calculate an average from a Sum by Month

Hi Guys,

I want the average from a time Period.

In one Table i have 2 Rows like this:

Date:                  Values:

01.01.2016        100

01.01.2016        50

01.01.2016        10

01.02.2016        50

01.02.2016        50

01.02.2016        20

If i creat visuals with average i only get the full average of all data ( 46,6666)

but i need it per Month  '
average  01/2016 = 53,33

02/2016 = 40

I tryed a lot of things with the little DAX i know but nothing worked out correctly.

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator

## Re: Calculate an average from a Sum by Month

Hi Mertsch,

I have tested it on my local environment, you can add calculated column to display the month name using the DAX below
Month = MONTH(MonthAverage[Date])

And then create a measure
MeasureAverage = SUM(MonthAverage[Total])/DISTINCTCOUNT(MonthAverage[Month])

Regards,

Charlie Liao

13 REPLIES 13
Frequent Visitor

## Re: Calculate an average from a Sum by Month

edit: sorry i explane it wrong....

what i need is:

the final average:

01/2016 = Sum 160

02/2016 = Sum 120

average: 140

Member

## Re: Calculate an average from a Sum by Month

Looks like you want to get the average by Summing the amount and dividing it by the number of months/distinct dates rather than dividing by the number of rows.

This might help, write following DAX measures:

Amount=SUM([Values])

NumOfDistinctDates=DISTINCTCOUNT([Date])

Avg=DIVIDE([Amount],[NumOfDistinctDates])

nikil

Check out the Chicagoland Power BI User Group

Super User

## Re: Calculate an average from a Sum by Month

Try creating a calculated column like:

`Month = MONTH([Date])`

### I have book! Learn Power BI from Packt

Proud to be a Datanaut!

Frequent Visitor

## Re: Calculate an average from a Sum by Month

well it wokrs for the full tabel but if i used filters it dont work for it.

( 140 is correct for all data )

But after using Filters it will be still 140.  it need to be 60 in that case

There must be a other way to have it more flexible with using filters

The data need to be calculated acording to the used filters and visual in front end.

May it works with GROUPBY Month Dates?! But i dont get the DAX work

= GROUPBY (Tabelle1;Tabelle1[Month];“TEST2”;SUM(CURRENTGROUP();Tabelle1[Total]))

Moderator

## Re: Calculate an average from a Sum by Month

Hi Mertsch,

I have tested it on my local environment, you can add calculated column to display the month name using the DAX below
Month = MONTH(MonthAverage[Date])

And then create a measure
MeasureAverage = SUM(MonthAverage[Total])/DISTINCTCOUNT(MonthAverage[Month])

Regards,

Charlie Liao

Frequent Visitor

## Re: Calculate an average from a Sum by Month

Thats what im looking for !

Thanks a lot !

Member

## Re: Calculate an average from a Sum by Month

Hi,

What if we want to use this measure with a date drill down, how would it work?

Fernando

Regular Visitor

## Re: Calculate an average from a Sum by Month

Hi,

I am a beginner in Power BI. I want to arrive at % of surgeries performed by each doctor for the month. i. e. total surgeries by a doctor in a month DIVIDED BY total surgeries in the month.

When I used the solution in a measure as "MonthlySurgeries = sum(tblReportPaed[Surgeries])/DISTINCTCOUNT(tblReportPaed[MonthNumber])" it generates an error at the bottom of the screen which reads as "TABLE: tblReportPaed (92 Rows) COLUMN: Measure (0 distinct values)".

Where am I going wrong?

Frequent Visitor

## Re: Calculate an average from a Sum by Month

This does not work if the data covers a period longer than a year as multiples will appear over multiple years, is there another way?

Announcements

Kudos to you if you earned one of these! Check your inbox for a notification.

#### Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

#### Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (4,382)