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

edit: sorry i explane it wrong.... 

 

what i need is:  

 

the final average:

01/2016 = Sum 160 

02/2016 = Sum 120

 

average: 140 

 

 

nikil's avatar
nikil
Icon for Resolver I rankResolver I
10 years ago

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

  • 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?

  • vikrammankar's avatar
    vikrammankar
    Frequent Visitor
    7 years ago

    Simple & Effective solution.  Cheers

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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.