Forum Discussion
Creating a multiplier column/measure from a given condition
Hi,
I wanted to create a multiplier column where I can multiply my salesprice column to a certain percentage based on the category id.
The sales price for this particular item that belongs in categoryid 7 should have an additional 4%. I was planning to create a multiplier column where I can simply multiply my sales price based on the categoryid that the product belongs. I was trying to work on switch functions but it does not work on measures.
I tried to make a desired output in excel that I wish to do in power bi.
Your help is highly appreciated.
Hello Anonymous
Switch statement is one way of doing it but it's not the best in terms of best practice. What if you have 40 categories with Multiplier. Do you want to update the expression in the Switch statement to include all of them?
What if you have 400 categories?
It's a better practice to store the multiplier in a different table then use Power query to grab the multiplier and calculate the column. You could also use the RELATED function in DAX and do a measure.
I did an example with the Power query approach. The NewSalesPrice is added inside Power query and the excel file has the CategoryMultiplier inside.
https://drive.google.com/drive/folders/1ACWTkk4yWTKLXKskWFrCpcoGrd-q6nfH?usp=sharing
2 Replies
- Nathaniel_C
Community Champion
Hi Anonymous
Please try this:The categories in the measure are 1 4 7 but the table has a 2 which has no multiplier to show an example.
Multiplier = VAR _getSales = MAX ( 'Table'[Salesprice] ) VAR _getCat = MAX ( 'Table'[Category] ) VAR _multiplier = SWITCH ( TRUE (), _getCat = "1", .10, _getCat = "4", .11, _getCat = "7", .15, 0 ) RETURN _getSales * ( 1 + _multiplier )
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel 
m3tr01dContinued Contributor
Hello Anonymous
Switch statement is one way of doing it but it's not the best in terms of best practice. What if you have 40 categories with Multiplier. Do you want to update the expression in the Switch statement to include all of them?
What if you have 400 categories?
It's a better practice to store the multiplier in a different table then use Power query to grab the multiplier and calculate the column. You could also use the RELATED function in DAX and do a measure.
I did an example with the Power query approach. The NewSalesPrice is added inside Power query and the excel file has the CategoryMultiplier inside.
https://drive.google.com/drive/folders/1ACWTkk4yWTKLXKskWFrCpcoGrd-q6nfH?usp=sharing
