cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)