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

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 

8 Replies

  • 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. 

       

       

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

    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's avatar
      GaryD
      Regular Visitor
      4 years ago

      Added the tables below, would have shared the PowerBI file but not comfortable sharing via OneDrive from work

       

      SaleRep Table:

       

      FacilityNames% Commission
      Fac1Peter5%
      Fac2Peter5%
      Fac3Paul5%
      Fac4Paul10%

       

      Sales Table:

       

      FacilitySales IDCountryAmountCostGross Profit
      Fac11234C110-55
      Fac11235C212-57
      Fac11236C110-55
      Fac21237C110-55
      Fac21238C110-55
      Fac21239C212-57
      Fac21240C212-57
      Fac31241C110-55
      Fac41242C110-55
      Fac41243C110-55
      Fac41244C212-57
      Fac41245C212-57
      Fac41246C212-57
      Fac41247C110-55

       

      Visual these create (note: previous visuals added Costs and not minus them):

       

       

      • GaryD's avatar
        GaryD
        Regular Visitor
        4 years ago

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