Forum Discussion

GaryD's avatar
GaryD
Regular Visitor
4 years ago
Solved

Using VALUES vs SUM to calculate commission

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 star...
Need Help
  • PaulDBrown's avatar
    PaulDBrown
    4 years ago

    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 

PaulDBrown's avatar
PaulDBrown
Icon for Community Champion rankCommunity Champion
4 years ago

Try:

Com ($) using SUM =
SUMX ( tbl_SaleRep, [Gross Proft] * SUM ( tbl_SaleRep[% Commission] ) )
GaryD's avatar
GaryD
Regular Visitor
4 years ago

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.