Using VALUES vs SUM to calculate commission

[GaryD]

Hi - having an issue when using a commission table where the % differs based on Facility and sales rep. I'm using a filter by the sales rep to get just thier sales so I can print this out.

I started using SUM but the subtotal then shows all the commissions and not for the selected -

Com ($) using SUM = [Gross Proft] * SUM(tbl_SaleRep[% Commission])

Changed to use VALUES which fixed the subtotals but then if a sales person has different % I get an error -

Com ($) using VALUES = [Gross Proft] * VALUES(tbl_SaleRep[% Commission])

Screenshots below:

Relationship

Paul - diff %

Peter - same %

Ideally what I need is to call the % based on Facilty and Name when the Name is filtered, but unable to figure this out and have not been able to find in the community.

Thanks in advance,

Gary

[PaulDBrown]

Can you share some sample data?

You probaly need a measure along the lines of:

Com($) =
SUMX (
ADDCOLUMNS (
SUMMARIZE ( tbl_SalesRep, 'tbl_SalesRep'[Facility], 'tbl_SalesRep'[Names] ),
"Com", [Gross Profit] * SUM ( 'tbl_SalesRep'[% Comission] )
),
[Com]
)

[GaryD]

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):

[GaryD]

My Gross Profit is a measure in PowerBI but I added the field in the table - that might make it slightly different

[PaulDBrown · Accepted solution]

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 

[GaryD]

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!!

[PaulDBrown]

Try:

Com ($) using SUM =
SUMX ( tbl_SaleRep, [Gross Proft] * SUM ( tbl_SaleRep[% Commission] ) )

[GaryD]

Thanks for the reply. Still not fixed. That works the same as -

Com ($) using SUM = [Gross Proft] * SUM(tbl_SaleRep[% Commission])

Problem I have with both of those is the subtotal does not equal the sum of the individual rows (below should be 15.90 not 55.50). Only way the subtotals work is with VALUES but then % commission needs to be the same.