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.
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.
Solved! Go to 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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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.
Dear @Fowmy @v-alq-msft ,
Really appreciate yous guys solution. Two of it works perfectly for me. Thanks!
Dear @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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@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)
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.
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 |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |