Forum Discussion

ADSL's avatar
ADSL
Post Prodigy
3 years ago
Solved

Calculate Sales Incentive by Client

Dear BI Expert,

 

Currently I am calcualted monthly sales incentive payout in excel. There are 02 KPIs for sales rep and 03 slabs as per attachment file.

1. Sales Achievement by Client (It's based on Weight for these clients)

2. Total Sales Achievement

https://drive.google.com/drive/folders/1h4G65DGngL5oYU6JadAA8PbdyowuaxSn?usp=sharing 

As per attachment file, Could we transform it from excel to power BI? Or get the total payout result in power BI?

 

Please kindly check and advise.

 

Best Regards,

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi ADSL ,

    I created a sample pbix file(see attachment) for you, please check whether that is what you want.

    1. Besides the excel file, assume that you have the below two tables(include target and contribution info)

    2. Create some measures as below:

    Sales = SUM('Data'[Amount No VAT])
    % Actual vs Target = 
VAR _selsupplier =
    SELECTEDVALUE ( 'Target'[Supplier Name] )
VAR _target =
    CALCULATE ( SUM ( 'Target'[Target] ), 'Target'[Supplier Name] = _selsupplier )
RETURN
    DIVIDE ( [Sales], _target )
    Points Scored = SUM ( 'Target'[Weight] ) * [% Actual vs Target]
    Payout (USD) = 
VAR _selsuplier =
    SELECTEDVALUE ( 'Target'[Supplier Name] )
VAR _usd =
    SWITCH (
        TRUE (),
        [% Actual vs Target] >= 0.9
            && [% Actual vs Target] < 0.9499, 45,
        [% Actual vs Target] >= 0.95
            && [% Actual vs Target] < 0.9999, 75,
        [% Actual vs Target] >= 1, 90
    )
VAR _contribution =
    CALCULATE (
        MAX ( 'Contribution'[Contribution%] ),
        'Contribution'[Supplier Name] = _selsuplier
    )
RETURN
    _usd * _contribution

    If the above one can't help you get the desired result, please provide more details(special examples, correct result etc.) on your requirement. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

    How to upload PBI in Community

    Best Regards

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi ADSL ,

    I created a sample pbix file(see attachment) for you, please check whether that is what you want.

    1. Besides the excel file, assume that you have the below two tables(include target and contribution info)

    2. Create some measures as below:

    Sales = SUM('Data'[Amount No VAT])
    % Actual vs Target = 
VAR _selsupplier =
    SELECTEDVALUE ( 'Target'[Supplier Name] )
VAR _target =
    CALCULATE ( SUM ( 'Target'[Target] ), 'Target'[Supplier Name] = _selsupplier )
RETURN
    DIVIDE ( [Sales], _target )
    Points Scored = SUM ( 'Target'[Weight] ) * [% Actual vs Target]
    Payout (USD) = 
VAR _selsuplier =
    SELECTEDVALUE ( 'Target'[Supplier Name] )
VAR _usd =
    SWITCH (
        TRUE (),
        [% Actual vs Target] >= 0.9
            && [% Actual vs Target] < 0.9499, 45,
        [% Actual vs Target] >= 0.95
            && [% Actual vs Target] < 0.9999, 75,
        [% Actual vs Target] >= 1, 90
    )
VAR _contribution =
    CALCULATE (
        MAX ( 'Contribution'[Contribution%] ),
        'Contribution'[Supplier Name] = _selsuplier
    )
RETURN
    _usd * _contribution

    If the above one can't help you get the desired result, please provide more details(special examples, correct result etc.) on your requirement. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

    How to upload PBI in Community

    Best Regards