Forum Discussion
Using VALUES vs SUM to calculate commission
This measure works:
Commission = SUMX(SalesRep, [Profit]* [% Commission])where
Profit = SUM (Sales [Profit])
and
% Commission = SUM( SalesRep [% Commission])
Or to make it simple
Commission = SUMX (SalesRep, CALCULATE(SUM(Sales [Profit])) * CALCULATE(SUM(SalesRep[% Commission]))
and
% Commission = IF(ISINSCOPE(SalesRep[Names]), SUM(SalesRep[% Commission]), DIVIDE([Commission], [Profit]))
I've attached the sample PBIX file
Added the tables below, would have shared the PowerBI file but not comfortable sharing via OneDrive from work
SaleRep Table:
| Facility | Names | % Commission |
| Fac1 | Peter | 5% |
| Fac2 | Peter | 5% |
| Fac3 | Paul | 5% |
| Fac4 | Paul | 10% |
Sales Table:
| Facility | Sales ID | Country | Amount | Cost | Gross Profit |
| Fac1 | 1234 | C1 | 10 | -5 | 5 |
| Fac1 | 1235 | C2 | 12 | -5 | 7 |
| Fac1 | 1236 | C1 | 10 | -5 | 5 |
| Fac2 | 1237 | C1 | 10 | -5 | 5 |
| Fac2 | 1238 | C1 | 10 | -5 | 5 |
| Fac2 | 1239 | C2 | 12 | -5 | 7 |
| Fac2 | 1240 | C2 | 12 | -5 | 7 |
| Fac3 | 1241 | C1 | 10 | -5 | 5 |
| Fac4 | 1242 | C1 | 10 | -5 | 5 |
| Fac4 | 1243 | C1 | 10 | -5 | 5 |
| Fac4 | 1244 | C2 | 12 | -5 | 7 |
| Fac4 | 1245 | C2 | 12 | -5 | 7 |
| Fac4 | 1246 | C2 | 12 | -5 | 7 |
| Fac4 | 1247 | C1 | 10 | -5 | 5 |
Visual these create (note: previous visuals added Costs and not minus them):
My Gross Profit is a measure in PowerBI but I added the field in the table - that might make it slightly different
PaulDBrownCommunity Champion
This measure works:
Commission = SUMX(SalesRep, [Profit]* [% Commission])where
Profit = SUM (Sales [Profit])
and
% Commission = SUM( SalesRep [% Commission])
Or to make it simple
Commission = SUMX (SalesRep, CALCULATE(SUM(Sales [Profit])) * CALCULATE(SUM(SalesRep[% Commission]))
and
% Commission = IF(ISINSCOPE(SalesRep[Names]), SUM(SalesRep[% Commission]), DIVIDE([Commission], [Profit]))
I've attached the sample PBIX file
Thanks Paul, and apologies for the delay...new job!
The ISINSCOPE worked a treat and loved how you seperate the measures in a new table!
- PaulDBrown
New job hey? Congratulations!!