Measure Total

[wildvike]

For our company's sales reps, we pay a commission based on the number of new accounts and average balance of those new accounts at a client level, not an overall level.  Below is what I put together for one of our reps:

For example, client# 164 placed 49 accounts with an average balance over $50k.  This results in a $500 commission for the sales rep for this client.  This is repeated for each of the clients assigned to the rep.  The measure for the Commission (Sign) column is:

The issue I'm running into is that it applies the measure to the total as well instead of summing the individual clients.  If you add up each individual client's commission, you would get $3,000, but the logic applies to the overall total of the two preceeding columns, giving the overall commission $3,750.

I'm looking for one of two options:

• Is there a way to sum the Commission (Sign) column instead of having the logic apply to the total, or
• Is there another measure to create that would get me the sum of the invidual clients' commissions where I can put the correct amount ($3000) in a card?

Thanks!

[DataZoe · Accepted solution]

@wildvike 

You can wriap the measure in a SUMX to get the total to do what you want.

SUMX(Values('Table'[Client #]), [Commission (Sign))])

[wildvike]

Thanks, that worked for me!