Measure - dividing a sum by a count gives me an error

[ansa_naz]

Hi all

I am trying to create a measure, I have got as far as the below:

Completion % = 
var thiscalendardate = MAX('DatesTally'[CalendarDate])
return
divide(sum(IF(MAX('Calls'[Completion Date]) > thiscalendardate, 1, 0)),
count(Calls[Job]))

I am trying to calculate the percentage of jobs scheduled per month which were completed in that month

So I have two tables, a Dates table called Datestally, which contains all of the schedule dates. And I have a table called Calls which contains all jobs with their completion dates:

Datestally table:

Date                 YearMonth

01/01/2018      2018-01

...

30/11/2018      2018-11

Calls table:

Job             Sch Date           Completion Date

125526      01/05/2018      30/05/2018

135524      02/05/2018      01/06/2018

135598      05/05/2018      29/05/2018

The measure will be used in a line chart, where the YearMonth is on the X axis, and I would want the measure on the Y axis.

For the above data, the measure would show:

2 / 3 = 66.66%

ie 2 jobs which were scheduled for May 2018 were also completed in May 2018

However, the measure I am usng above is returning an error at the moment:

The SUM function only accepts a column reference as an argument

Any ideas where I could be going wrong??

Many thanks for all help

Cheers

[v-lili6-msft]

hi, @ansa_naz

After my test, SUM needs a column not  you could try this formula as below:

Add a year month column for column Sch Date or Completion Date.

then 

Completion % = 
var thiscalendardate = MAX('DatesTally'[Date])
return
divide(sumx(FILTER(Calls,Calls[Completion YEAR MONTH]=MAX(DatesTally[YEAR MONTH])),IF(MAX('Calls'[Completion Date]) > thiscalendardate, 1, 0))
,count(Calls[Job]))

here is pbix, please try it.

https://www.dropbox.com/s/425glnid46tdron/Measure%20-%20dividing%20a%20sum%20by%20a%20count%20gives%...

Best Regards,

Lin

[affan · Accepted solution]

Hi @ansa_naz

Please try this measure

Completion % = 
var thiscalendardate = MAX('DatesTally'[CalendarDate])
return
divide(CALCULATE(COUNTROWS(Calls),Filter(Calls,'Calls'[Completion Date] > thiscalendardate)),
count(Calls[Job]))

If this helped you, please mark this post as an accepted solution and like to give KUDOS .

Regards,

Affan

[ansa_naz]

Thanks @affan works brill!