cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper IV

## Create a measure or calculated column that gives the division between an integer and a text variable

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:

• Available Stock is a Integer variable in a fact table
• Packing Qty per Box is a tex variable in a dimension table
• There is a relation of 1 to many between the dimension and the fact table

I replicated the problem in a dummy file and a screen shot. Could you please help me?

Thanks a lot!

1 ACCEPTED SOLUTION
Super User

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 )
)``````
4 REPLIES 4
Super User

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 )
)``````
Helper IV

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!

Super User
``````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
)``````
Helper IV

Thank you so so much!