Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
jessicarocha
Helper IV
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?

 

jessicarocha_1-1662706375099.png

Thanks a lot!

 

Dummy PBI 

 

 

 

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

Hi @jessicarocha 
Please refer to attached sample file with the solution

1.png

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

View solution in original post

4 REPLIES 4
tamerj1
Super User
Super User

Hi @jessicarocha 
Please refer to attached sample file with the solution

1.png

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! 

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

Thank you so so much!

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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