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

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

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's avatar
    PaulDBrown
    Icon for Community Champion rankCommunity Champion
    4 years ago

    New job hey? Congratulations!!