cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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!

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors