Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ansa_naz
Continued Contributor
Continued Contributor

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

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

1 ACCEPTED SOLUTION
affan
Solution Sage
Solution Sage

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

View solution in original post

3 REPLIES 3
v-lili6-msft
Community Support
Community Support

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
affan
Solution Sage
Solution Sage

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
Continued Contributor
Continued Contributor

Thanks @affan works brill!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.