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

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

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.