cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
jessicarocha
Advocate II
Advocate II

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
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors