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

[jessicarocha]

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 

[tamerj1 · Accepted 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 )
)

[jessicarocha]

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! 

[tamerj1]

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

[jessicarocha]

Thank you so so much!