Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
Hello everyone,
I am trying to create a measure or a calculated column that gives me the division between a numeric and a text variable. I don't have access to Power Query and changing data type is blocked for me, so I can only achieve this with DAX.
I read many posts. I tried to use CONVERT and VALUE but I am clearly doing something wrong.
There are two problems:
1. The division between a numeric and text variable (Available Stock / Packing Qty per Box)
2. Even with the creation of this variable would be sucessful, the TOTAL in the end of the table, would also be a division. However, I would like the total to be a sum. I would like the total to give me the total amount of boxes in Stock.
Technical Information:
I replicated the problem in a dummy file and a screen shot. Could you please help me?
Thanks a lot!
Solved! Go to Solution.
Hi @jessicarocha
Please refer to attached sample file with the solution
Boxes in Stock =
SUMX (
'Material Dimension',
VAR AvialableStock = SUMX ( RELATEDTABLE ( Stock ), Stock[Available Stock] )
VAR PackQty = VALUE ( 'Material Dimension'[Packing Qty per Box] )
RETURN
DIVIDE ( AvialableStock, PackQty )
)
Hi @jessicarocha
Please refer to attached sample file with the solution
Boxes in Stock =
SUMX (
'Material Dimension',
VAR AvialableStock = SUMX ( RELATEDTABLE ( Stock ), Stock[Available Stock] )
VAR PackQty = VALUE ( 'Material Dimension'[Packing Qty per Box] )
RETURN
DIVIDE ( AvialableStock, PackQty )
)
Thank you so much @tamerj1 ! This is exactly what I need!
I forgot the fact that the stock fact is also connected to a date dimension. I am only interested in the stock representing last week, so I would like to add a filter for that.
I tried to adapt that in your formula but I can't make it work. I added in the SUMX from the VAR Available Stock.
FILTER('Date Dimension', 'Date Dimension'[Week(yyyyWww)]= (YEAR(TODAY()) &"W" & WEEKNUM(TODAY(),21)-1) )
But it does not work. I am unsure where to add the filter. Can you also help with that?
Thank you so much, really!
Boxes in Stock =
CALCULATE (
SUMX (
'Material Dimension',
VAR AvialableStock =
SUMX ( RELATEDTABLE ( Stock ), Stock[Available Stock] )
VAR PackQty =
VALUE ( 'Material Dimension'[Packing Qty per Box] )
RETURN
DIVIDE ( AvialableStock, PackQty )
),
'Date Dimension'[Week(yyyyWww)]
= YEAR ( TODAY () ) & "W"
& WEEKNUM ( TODAY (), 21 ) - 1
)
Thank you so so much!
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
14 | |
10 | |
10 | |
9 | |
9 |
User | Count |
---|---|
20 | |
13 | |
12 | |
11 | |
8 |