Convert C to DAX

[NickProp28]

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.

[Fowmy · Accepted solution]

@NickProp28

Try these two measurements for 20 and 10

20 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 🙂

YoutubeLinkedin

[v-alq-msft · Accepted solution]

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.

[v-alq-msft · Accepted solution]

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 · Accepted solution]

@NickProp28

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 🙂

YouTube, Linkedin

[NickProp28]

Dear @Fowmy ,

Thank you. 

[Fowmy · Accepted solution]

@NickProp28

Try these two measurements for 20 and 10

20 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 🙂

YoutubeLinkedin

[amitchandak]

@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)

[NickProp28]

Dear @amitchandak,

Thanks for your prompt response,

I would like to create a measure based on no of packages, but im getting problem on it.