Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
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:
I replicated the problem in a dummy file and a screen shot. Could you please help me?
Thanks a lot!
Solved! Go to 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 )
)
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 )
)
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!
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!
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.