Forum Discussion
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 able to select (or not select) a product group and change the commission percent for that product group (in increments of 1/2% and then apply against Net Sales to recalculate the commission.). Would need to be able to do this same excercise with multiple product groups.
Hi BUNJ
Could you please provide sample data that fully represents your issue or question, in a usable format (e.g., Excel or CSV, not as a screenshot)?
-
Please ensure no sensitive or unrelated information is included.
-
Also, share the expected outcome based on the sample data you provide.
How to provide sample data in the Power BI Forum - Microsoft Fabric Community
-
7 Replies
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: CommissionParameterThen 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)
Thanks for replying. This is what I am trying to do
Hi BUNJ
Could you please provide sample data that fully represents your issue or question, in a usable format (e.g., Excel or CSV, not as a screenshot)?
-
Please ensure no sensitive or unrelated information is included.
-
Also, share the expected outcome based on the sample data you provide.
How to provide sample data in the Power BI Forum - Microsoft Fabric Community
-
- Create a slicer for Product Group and another for Commission Percent (0.5% to 8%).
- Use a disconnected table for commission options.
- Write a DAX measure that applies the selected commission percent only to selected product groups.
- If no group is selected, it uses the default commission.
This lets you dynamically override commissions for multiple groups and see the impact instantly.
