Forum Discussion
Commission Calculations
Hi, I am relatively new to Power BI, I have the basics but seem to always spend a day or two struggling to do more advanced formulas. I have 3 simple tables:
1. Sales table that shows invoices with item level detail.
2. Commission table that shows sales person and % Commission paid based on item category being sold.
3. Summary Table that shows total sales, costs, and margin for each invoice (sums sales for all items for each Invoice).
The compensation logic is: If the total invoice is greater than $50, or is less than -$50 (this takes into consideration large returns), multiply the individual item sale margin amount by the % commission for that category.
I created a measure but it doesn't work.
Sales[Margin] * RELATED('Commission Table'[Comp %]), 0)))
Can someone tell me what is wrong with my measure...or if there is a better way to compute the compensation formula?
Thank you!!!!
Gene
Compensation =
CALCULATE (
SUMX ( Sales, Sales[Margin] * RELATED ( 'Commission Table'[Comp %] ) ),
FILTER ( 'Invoice Summary', ABS ( 'Invoice Summary'[Invoice Sales] ) > 50 || ABS ( 'Invoice Summary'[Invoice Sales] ) < -50 )
)Try adding an || (OR) statement to your Filter...
4 Replies
Hi Gene,
Give this a try:
Compensation =
CALCULATE (
SUMX ( Sales, Sales[Margin] * RELATED ( 'Commission Table'[Comp %] ) ),
FILTER ( 'Invoice Summary', ABS ( 'Invoice Summary'[Invoice Sales] ) > 50 )
)Thanks!
Matt
Matt,
Thanks for responding, your formula works however I am not sure it is covering the invoice items with a value less than -$50. It appears to only work on invoices that are > $50. How do I cover both ranges < -50 or >50?
Very much appreciate your help!
Compensation =
CALCULATE (
SUMX ( Sales, Sales[Margin] * RELATED ( 'Commission Table'[Comp %] ) ),
FILTER ( 'Invoice Summary', ABS ( 'Invoice Summary'[Invoice Sales] ) > 50 || ABS ( 'Invoice Summary'[Invoice Sales] ) < -50 )
)Try adding an || (OR) statement to your Filter...
