Forum Discussion

mertsch's avatar
mertsch
Frequent Visitor
10 years ago
Solved

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.20...
Need Help
  • v-caliao-msft's avatar
    v-caliao-msft
    10 years ago

    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

mertsch's avatar
mertsch
Frequent Visitor
10 years ago

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

 

v-caliao-msft's avatar
v-caliao-msft
Icon for Microsoft Employee rankMicrosoft Employee
10 years ago

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

  • Kinsey's avatar
    Kinsey
    Icon for Advocate III rankAdvocate III
    9 years ago

    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's avatar
      drmbrklyn
      Frequent Visitor
      9 years ago

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

  • mertsch's avatar
    mertsch
    Frequent Visitor
    10 years ago

    Thats what im looking for ! 

    Thanks a lot ! 

     

  • calerof's avatar
    calerof
    Icon for Impactful Individual rankImpactful Individual
    10 years ago

    Hi,

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

    Fernando

     

  • deepvibha's avatar
    deepvibha
    Icon for Advocate II rankAdvocate II
    9 years ago

    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?

     

    Appreciate in advance.

     

    • Ramanji's avatar
      Ramanji
      Frequent Visitor
      4 years ago

      Hi 

      I have similar report but it has 3 years data.

      I'm looking for average calculation as below

      =total year production value/no. of months

      kindly provide solution

  • sujith31's avatar
    sujith31
    Frequent Visitor
    4 years ago

    Thanks, this helps.

    But, I have a slightly different scenario. 

    I am looking at tickets closed by users in a date range.
    In report, say, I selected the Closed Date Range as 1/Aug - 31/Jan (6 Months)

    Now, I am facing the following mismatch.

    UserTickets ClosedDistinct Closed Months

    Calculated Average

    		Actual Average
    A15

    Aug2021,

    Sep2021,

    Oct2021,

    Jan2022

    		5/4=1.255/6=0.83
    A23Aug2021,
    Sep2021    		3/2=1.53/6=0.5


    These users worked all six months - but they closed no tickets in some months. Still the average is showing better than actual.

    How do I rectify this?