topic Create a measure or calculated column that gives the division between an integer and a text variable in DAX Commands and Tips

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

jessicarocha — Fri, 09 Sep 2022 06:57:15 GMT

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!

Dummy PBI

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

tamerj1 — Fri, 09 Sep 2022 08:04:23 GMT

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

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

jessicarocha — Fri, 09 Sep 2022 08:34:40 GMT

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!

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

tamerj1 — Fri, 09 Sep 2022 08:58:23 GMT

jessicarocha

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 )

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

jessicarocha — Fri, 09 Sep 2022 10:22:29 GMT

Thank you so so much!