Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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")
Hi @Anonymous,
Please check the data type of the column [Margin]. If it is text, the formula will return #ERROR and you should change it to decimal number.
Thanks,
Yuliana Gu
Is margin a measure or a calc column? Is DivisibleBy5 a measure or a calc column?
Well it looks right. Can you post a sample of the values in the margin column?
here is my formula and it works fine. So I have to think something strange is happening to your data column. Do you have any rougue values somewhere in the column? Try writing a calc column =Max(table[total cost margin]) and see what you get
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")
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
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.
Hey @Anonymous,
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |