cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rynoh17 Member
Member

Margin Divisible by 5?

Hi,

I am looking to find a calculation that will tell me whether an item was sold at a margin divisible by five or not. i.e. ..., 35%, 40%, 45%, 50%, ... etc. I figured it would be simple using the MOD function. I tried each of the following

DivisibleBy5 = IF(MOD([Margin], 0.05) = 0, "Yes", "No")
DivisibleBy5 = IF(MOD([Margin] * 100, 5) = 0, "Yes", "No")

 Each give me an error saying "An argument of function 'MOD' has the wrong data type or the result is too large or too small". 

 

My [Margin] is a decimal number and classified as such. Any idea what's going wrong? Any other ways to get the result I'm looking for?

 

Thanks, 
Ryan

1 ACCEPTED SOLUTION

Accepted Solutions
rynoh17 Member
Member

Re: Margin Divisible by 5?

@MattAllington

 

Sale Prices of $0.00 were creating -infinity values which was screwing it up. I stacked another IF statement on top of the other, saying IF([Sale]=0, IF(MOD([Margin], .05) = 0, "True", "False"), "False")

View solution in original post

12 REPLIES 12
rynoh17 Member
Member

Margin Divisible by 5?

Hi,

I am looking to find a calculation that will tell me whether an item was sold at a margin divisible by five or not. i.e. ..., 35%, 40%, 45%, 50%, ... etc. I figured it would be simple using the MOD function. I tried each of the following

DivisibleBy5 = IF(MOD([Margin], 0.05) = 0, "Yes", "No")
DivisibleBy5 = IF(MOD([Margin] * 100, 5) = 0, "Yes", "No")

 Each give me an error saying "An argument of function 'MOD' has the wrong data type or the result is too large or too small". 

 

My [Margin] is a decimal number and classified as such. Any idea what's going wrong? Any other ways to get the result I'm looking for?

 

Thanks, 
Ryan

alanhodgson Established Member
Established Member

Re: Margin Divisible by 5?

Hey @rynoh17,

 

The MOD function can be expressed in terms of the INT function: MOD(n, d) = n - d*INT(n/d)

 

Try this and see if the same error occurs:

 

DivisibleBy5 = IF(([Margin] - (0.05)*INT([Margin]/0.05)) = 0, "Yes", "No")

Hope this helps,

 

 

Alan

Re: Margin Divisible by 5?

Try creating a calculated column that just uses the inner formula

Eg. Column = mod ([margin] , 5)

And what does that show?

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

rynoh17 Member
Member

Re: Margin Divisible by 5?

@alanhodgson 

That results in an error. "The result of a conversion or arethmatic operation is either too large or too small.

@Phil_Seamark 

The calculated column  gives me "An argument of function 'MOD' has the wrong data type or the result is too large or too small" again. 

Re: Margin Divisible by 5?

Try MOD (int ([margin]*100) ,5)

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Super User
Super User

Re: Margin Divisible by 5?

Is margin a measure or a calc column? Is DivisibleBy5 a measure or a calc column?



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
rynoh17 Member
Member

Re: Margin Divisible by 5?

@MattAllington 

 

Both are calculated columns. I want it to calculate per row.

Super User
Super User

Re: Margin Divisible by 5?

Well it looks right. Can you post a sample of the values in the margin column?



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
rynoh17 Member
Member

Re: Margin Divisible by 5?

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)