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

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.