Forum Discussion
Post PartisanConvert C to DAX
Dear Community,
This is part of my dataset import from Excel
| Country | No of package | Month |
| US | 1 | January |
| SG | 2 | January |
| US | 7 | February |
| US | 4 | January |
This is the total sum of No packages with month.
I have total packages of this items and would like to have a DAX measure to determine how many pallet size to get on it.
I have 10size pallet and 20size pallet.
For example, January I have 247items, so calculate it will require twelve 20size pallet and one 10 size pallet. (247/20 = 12.35, remainder will go into the 10size pallet.)
In C language,
if totalitem > 20,
{
Num20 = totalitem/20
Remain1 = totalitem % 20
}
if Remain1 > 0
{
Num10 = Remain1/10
Remain2 = Remain1 % 10
}
And my expected outcome will like
| Country | Months | No of Packages | Measure |
| US | January | 247 | 12 20s pallet and 1 10s pallet. |
I appreciate you taking the time to answer my question. I am sure you are very busy and thank you for taking the time to personally respond to me. Thanks again.
@NickProp28
Try these two measurements for 20 and 1020 Plt = INT(DIVIDE(SUM(TABLE[NO OF PACKAGES]),20)) 10 Plt = ROUNDUP(MOD(sum(TABLE[NO OF PACKAGES]),20),-1)/10________________________
Did I answer your question? Mark this post as a solution, this will help others!.
Click the Thumbs-Up icon on the right if you like this answer 🙂
Hello, @NickProp28
According to your description, I created data to reproduce your scenario. The pbix file is attached at the end.
Mesa:
You can create a measure like the following.
Result = var _value = SUM('Table'[No of Packages]) var _num20 = INT(DIVIDE(_value,20)) var _remain1 = MOD(_value,20) var _num10 = ROUNDUP(DIVIDE(_remain1,10),0) var _remain2 = MOD(_remain1,10) return IF( _value>20, IF( _remain1>0, _num20&" 20s pallet and "&_num10&" 10s pallet", _num20&" 20s pallet" ), INT(DIVIDE(_value,10))&" 10s pallet" )Result:
Best regards
Allan
If this post helps,then consider Accepting it as the solution to help other members find it more quickly.
It rounds one decimal place to the left of the decimal point.
________________________
Did I answer your question? Mark this post as a solution, this will help others!.
Click on the Thumbs-Up icon on the right if you like this reply 🙂
7 Replies
- amitchandak
Super User
NickProp28 , quotient and mod will help
https://docs.microsoft.com/en-us/dax/quotient-function-dax
https://docs.microsoft.com/en-us/dax/mod-function-dax
quotient([column],20)
mod([column],20)
NickProp28Dear amitchandak,
Thanks for your prompt response,
I would like to create a measure based on no of packages, but im getting problem on it.
- Fowmy
@NickProp28
Try these two measurements for 20 and 1020 Plt = INT(DIVIDE(SUM(TABLE[NO OF PACKAGES]),20)) 10 Plt = ROUNDUP(MOD(sum(TABLE[NO OF PACKAGES]),20),-1)/10________________________
Did I answer your question? Mark this post as a solution, this will help others!.
Click the Thumbs-Up icon on the right if you like this answer 🙂
v-alq-msftCommunity Support
Hello, @NickProp28
According to your description, I created data to reproduce your scenario. The pbix file is attached at the end.
Mesa:
You can create a measure like the following.
Result = var _value = SUM('Table'[No of Packages]) var _num20 = INT(DIVIDE(_value,20)) var _remain1 = MOD(_value,20) var _num10 = ROUNDUP(DIVIDE(_remain1,10),0) var _remain2 = MOD(_remain1,10) return IF( _value>20, IF( _remain1>0, _num20&" 20s pallet and "&_num10&" 10s pallet", _num20&" 20s pallet" ), INT(DIVIDE(_value,10))&" 10s pallet" )Result:
Best regards
Allan
If this post helps,then consider Accepting it as the solution to help other members find it more quickly.
- NickProp28
Dear @Fowmy @v-alq-msft ,
Really appreciate yous guys solution. Two of it works perfectly for me. Thanks!
Dear @Fowmy .10 Plt = ROUNDUP(MOD(sum(Data[NO OF PACKAGES]),20),-1)/10 . May I know why there have -1 in this formula. Thank you for providing me with advice.- Fowmy
It rounds one decimal place to the left of the decimal point.
________________________
Did I answer your question? Mark this post as a solution, this will help others!.
Click on the Thumbs-Up icon on the right if you like this reply 🙂
