Forum Discussion
DAX Measure to divide two columns based values in third column
Hi, I am looking for a DAX measure. I want to use a DAX measure to divide cost with quantity based on rollout column. e.g. in below table if the value in Rollout is "Full" then divide sum of Cost(Y1) with sum of Quantity(Y1). But if Rollout value is "Half" then divide sum of Cost(Y1) with sum of Quantity(Y2).
Looking forward for your support.
geetika3110 Give it a try
Res =
VAR currentRollout = SELECTEDVALUE(Data[Rollout])
RETURN
SWITCH(
currentRollout,
"Full", DIVIDE(SUM(Data[Cost (Y1)]), SUM(Data[Quantity (Y1)])),
"Half", DIVIDE(SUM(Data[Cost (Y1)]), SUM(Data[Quantity (Y2)])),
BLANK()
)
3 Replies
geetika3110 Give it a try
Res =
VAR currentRollout = SELECTEDVALUE(Data[Rollout])
RETURN
SWITCH(
currentRollout,
"Full", DIVIDE(SUM(Data[Cost (Y1)]), SUM(Data[Quantity (Y1)])),
"Half", DIVIDE(SUM(Data[Cost (Y1)]), SUM(Data[Quantity (Y2)])),
BLANK()
)Thank you so much for your support. I have to use the measure as I need to divide the aggregation of cost with the aggregation of quantity. The measure you suggested works. Thank you again
Hi geetika3110
Would a calculated column like this work?
(The reason I chose a calculated column is that all required fields are in the same record. If the result needed values from different rows (or an aggreagation), then a measure might be the way to go. Then this column can be aggregated like any other.)
Result = IF( [Rollout] = "Full", DIVIDE( [Cost (Y1)], [Quantity (Y1)] ), DIVIDE( [Cost (Y1)] , [Quantity (Y2)] ) )Let me know if you have any questions.
