Forum Discussion

BUNJ's avatar
BUNJ
Frequent Visitor
1 year ago
Solved

Dynamically applying commission percents

I have a table containing"Product Group", "Commission Percent" and "Net Sales".  There are approximately 50 product groups and the existing commission percent ranges from 1-8%.  I would like to be ab...
Need Help
Cookistador's avatar
Cookistador
Super User
1 year ago

To achieve this goal, you can create a new numerical parameter in Power BI with the following (Model ---> New Parameter --> Numerical)

 

In the window you can set up your paremters (behind the scene, it will create a disconnected table)
In our case, let call it: CommissionParameter

 

Then you just have to create a measure like this one:

 

Total New Commission =
VAR SelectedProductGroup = SELECTEDVALUE('YourTable'[Product Group])
VAR NewCommissionRate = SELECTEDVALUE('CommissionParameter'[New Commission %])
RETURN
SUMX(
'YourTable',
IF(
NOT ISBLANK(SelectedProductGroup) && NOT ISBLANK(NewCommissionRate) && 'YourTable'[Product Group] = SelectedProductGroup,
'YourTable'[Net Sales] * NewCommissionRate,
'YourTable'[Net Sales] * 'YourTable'[Commission Percent]
)
)

 

If it is not what you are trying to achieve, do not hesistate to give more details (like some dummy data sample)

  • BUNJ's avatar
    BUNJ
    Frequent Visitor
    1 year ago

    Thanks for replying.  This is what I am trying to do

     

      • v-aatheeque's avatar
        v-aatheeque
        Community Support
        1 year ago

        Hi BUNJ 

        To help us reproduce and troubleshoot your scenario, we requested to share a small sample dataset/file. Could you please provide that when convenient?
        It will allow us to give you a more accurate and tailored solution.