Forum Discussion
Multiplier depending on critera
Hello,
I would like to create a measure or column based off critera that will dicate to multiply a set of data or not. In the table below, I would like to multiply anything that has the channel of "Disty" by 6%. Any help would be great! Thank you!
| Channel | Cost |
| Direct | $ 100.00 |
| Disty | $ 200.00 |
| Disty | $ 100.00 |
| Direct | $ 500.00 |
| Disty | $ 600.00 |
Hey there Anonymous,
What you need to do is create a DAX Calculated column. The formula should look like this:
Column Name = If('Table'[Channel] = "Disty", 'Table'[Cost] * 1.06, 'Table'[Cost])
If this is correct please think about marking it as a solution.Oh you're saying you just don't want the Direct cost? Only disty?
Disty Cost = CALCULATE ( SUM ( 'table'[cost] ), 'table'[Channel] = "Disty" ) * 1.06
12 Replies
Hey there Anonymous,
What you need to do is create a DAX Calculated column. The formula should look like this:
Column Name = If('Table'[Channel] = "Disty", 'Table'[Cost] * 1.06, 'Table'[Cost])
If this is correct please think about marking it as a solution.This works, but when I filter on other things in the report, it is multiplying all cost still instead of what is filtered. Anyway to get around this? For instance. If had the same table as above but added in one more column for filtering. Example below.
Company Channel Cost A Direct $ 100.00 B Disty $ 200.00 C Disty $ 100.00 A Direct $ 500.00 A Disty $ 600.00
___measure = VAR disty = CALCULATE ( SUM ( 'table'[cost] ), 'table'[Channel] = "Disty" ) * 1.06 VAR other = CALCULATE ( SUM ( 'table'[cost] ), 'table'[Channel] <> "Disty" ) RETURN other + distyWill this work for you?
Now you have a solution for a column or a measure!
A few things to consider when choosing a calculated column vs measure.
https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/#:~:text=The%20difference%20is%20the%20context,the%20table%20it%20belongs%20to.
Try the measure I posted earlier to see if you get your desired result.