New Conditional Column Calculation based on (non-value) other column entry

[Anonymous]

Hi, I'm relatively new to PBI and I wanted to create a column showing the 'Bonus' amount (the new 'outcome' column) but based on a calculation of either ("Sales Rev" x "Multiplier") or ("Sales+Referrals Rev" x "Multiplier)".

So for example, if the entry in Column B ("Level") = 'Junior', the multiply column C ("Sales Rev" which is itself a sum of certain columns) by the column E ("Multiplier", which is itself a calculated field). 

But if the entry in Column B("Level") = 'Exec', the multiply column D ("Sales Rev + Referrals" which is itself a sum of different columns) by the column E ("Multiplier"). 

Hope that makes sense.

I'm working in the Transform Data table at the moment, and would appreciate any guidance!

I've copied the data back into excel to illustrate what i'm trying to show...

Much appreciated,

Bx

[Anonymous · Accepted solution]

Hi @Anonymous ,

Based on the calculation logic provided in your screenshot (Columns J and K), it seems that the calculation logic you describe and the calculation results do not match. If the values in column J are correct, then the calculation logic should be as follows:
If Level = "Junior", then it is (Sales Rev1+Sales Rev2+Sales Rev3)*2;
If Level = "Exec", then it is (Sales Rev1+Sales Rev2+Sales Rev3+Sales Rev4)*3;
If it is other level, it is 0.
If my understanding above is correct, you can create the following calculated column or measure to get the Bonus... Please find the details in the attachment.

Calculated column:

Bonus = 
VAR _srev = 'Table'[Sales Rev1] + 'Table'[Sales Rev2] + 'Table'[Sales Rev3]
VAR _srefrev = 'Table'[Sales Rev1] + 'Table'[Sales Rev2] + 'Table'[Sales Rev3] + 'Table'[Sales Rev4]
RETURN
    SWITCH ( 'Table'[Level], "Junior", _srev * 2, "Exec", _srefrev * 3, 0 )

Measure:

Measure_Bonus = 
VAR _srev =
    SUM ( 'Table'[Sales Rev1] ) + SUM ( 'Table'[Sales Rev2] )
        + SUM ( 'Table'[Sales Rev3] )
VAR _srefrev =
    SUM ( 'Table'[Sales Rev1] ) + SUM ( 'Table'[Sales Rev2] )
        + SUM ( 'Table'[Sales Rev3] )
        + SUM ( 'Table'[Sales Rev4] )
RETURN
    SWITCH (
        SELECTEDVALUE ( 'Table'[Level] ),
        "Junior", _srev * 2,
        "Exec", _srefrev * 3,
        0
    )

Best Regards

[Anonymous]

Brilliant - thank you so much - it was the formula that I was looking for, so thank you so much for helping me out with that! And you're quite right re the columns (they weren't the important bits, I'd simplified them to be able to share, but thank you)!

[tamerj1]

Hi @Anonymous 
You may start with presenting sample of your source data and then build on it your expected result. Power Bi works in a different way than excel does therefore, it is better that first you clearly define the starting point along with the expected outcome then you can provide more details about how the numbers are calculated.