A column that calculates status based on division of other columns

[Anonymous]

Hi All,

I have table that stores drivers' business miles driven and the days reported. The business has asked to set a status as either 'Compliant' or not 'Not-Compliant' based on the formula ->

Annualized Cummulative Business Miles  = Total Business Miles/Total Days Reported * 365

I created a calculated column that tells if a particular driver is compliant or not. If a driver crosses 12k miles, then he/she is compliant, otherwise not.

The issue I am facing is that drivers with less than 12k miles are being placed as 'Compliant'

Here is the DAX function I used ->

Compliance Status = IF(DIVIDE(SUM(fleet[Total Business Miles]),SUM(fleet[Total # Days Reported])) * 365 >= 12000, "Compliant", "Non-Compliant")

Since my data source is a blob storage where we receive monthly files that will certainly have duplicate values, is that the reason why I'm getting this error.Please refer to the image above. 
I apologize if this questions seems repetitive.

[VasTg]

@Anonymous 

Try a bracket..

IF((DIVIDE(SUM(fleet[Total Business Miles]),SUM(fleet[Total # Days Reported])) * 365 )>= 12000, "Compliant", "Non-Compliant")

If it helps, mark it as a solution

Kudos are nice too

[Anonymous]

@VasTg- Thanks! I'll try using brackets. In this scenario, it'll be -

Compliance Status = IF(DIVIDE((SUM(fleet[Total Business Miles])*365),SUM(fleet[Total # Days Reported])) >= 12000, "Compliant", "Non-Compliant")

Btw, I created separate measures for Total Business Miles, Total Days reported, then made a new measure with projected business miles. Finally, I created a new column ->

Status = 

Status = IF('Measure Table'[M Projected Miles] >= 12000, "Compliant", "Not-Compliant")

This has solved the issue for me

[tkirilov]

Hi @Anonymous ,

Try this:

Compliance Status = IF(DIVIDE(SUM(fleet[Total Business Miles]),(SUM(fleet[Total # Days Reported])) * 365) >= 12000, "Compliant", "Non-Compliant")

[Anonymous · Accepted solution]

@tkirilov., thanks for your help. I guess the issue for that I was using a Calculated Column inplace of a measure.

Using this measure - 

New Compliance = IF(DIVIDE(SUM(fleet[Business Miles]) *365,SUM(fleet[Days Reported]),0) >=12000, "Compliant", "Not Compliant")

seems to have solved the issue.

Please look at the image belowThe above image shows the new measure works

[Anonymous]

@tkirilov- This is making every row non-compliant