cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mertsch
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. 

 

thank you in advance

 

1 ACCEPTED SOLUTION

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

Capture.PNGCapture1.PNGCapture2.PNGCapture3.PNG

Regards,

Charlie Liao

View solution in original post

13 REPLIES 13
MartaRamos
Frequent Visitor

I have the quantity of employee per month, need to calculate the average per month

Final Qty                  Average per month

January :100            100

February: 120           110

March:   90               105

April ::  100               95

 

Average= (Qty 1+Qty)/2

i am struggling with this , if someone can help me, 🙂 😞 

mertsch
Frequent Visitor

edit: sorry i explane it wrong.... 

 

what i need is:  

 

the final average:

01/2016 = Sum 160 

02/2016 = Sum 120

 

average: 140 

 

 

Try creating a calculated column like:

 

Month = MONTH([Date])

Then add your Average measure and Month to a table.


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

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

Hey,

 

I have one more question here, i ahave one Power Bi template, which has Hours by month Operating Group wise.

 

Cureently in Power Bi i am using Matrx visual for this and i have the data from February 2018 to December 2018, i .e for 10 months.

 

But when i used the formual 

 

Average Hours = SUM('MC Payroll and Hours'[Hours]) / DISTINCTCOUNT('Dates'[Month].[Month])
 
For 
DISTINCTCOUNT('Dates'[Month].[Month])    it was taking months count as 12 not 10, is there any way that i can write code for taking only 10 months.(of visual has 10 months data, months count should come to 10, if visual has data for 8 months, count should come to 8.)
 
My Financial Year is September to Aug.
 
thanks.   
 
 
 

Simple & Effective solution.  Cheers

mertsch
Frequent Visitor

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

 

( 140 is correct for all data ) 

 

 A.JPG

 

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

 

 

B.JPG

 

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

 

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

Capture.PNGCapture1.PNGCapture2.PNGCapture3.PNG

Regards,

Charlie Liao

View solution in original post

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?

drmbrklyn
Frequent Visitor

substitute the followign for the month formula:  =format([date], "YYYYMM")

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. 

 

Data.png

 

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?

 

Appreciate in advance.

 

calerof
Impactful Individual
Impactful Individual

Hi,

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

Fernando

 

Thats what im looking for ! 

Thanks a lot ! 

 

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors