Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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. 

 

JeanoVipinosa09_0-1638088034825.png

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.

 

JeanoVipinosa09_1-1638088408898.png

 

 

Your help is highly appreciated.

 

@amitchandak , @Greg_Deckler 

1 ACCEPTED SOLUTION
m3tr01d
Continued Contributor
Continued 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

 

 

View solution in original post

2 REPLIES 2
m3tr01d
Continued Contributor
Continued 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

 

 

Nathaniel_C
Super User
Super User

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.

 

 

1.PNG

 

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.