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

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

vikrammankar's avatar
vikrammankar
Frequent Visitor
7 years ago

Simple & Effective solution.  Cheers