Forum Discussion
Commissions balance report
Hello
I would like to have a way to control the salesmen commissions versus what to pay and at the final get a kind an account balance per salesmen, any tips ideas are more than welcome.
As example the desire output :
I have monthly fixed monthly commissions column D
At quarter I calculate the commission,base on sales, and will pay 50% column E , the remaining 50% add to year end Column F
I have all measures calculated just I'm not seeing how I can set a report like this.
Appreciate your help
Hi pmargari ,
Since your screenshot doesn't show sales, it's not entirely clear to me how column E in your table is calculated. I did some calculations using my "financials" table and I hope this helps.
1. Create a measure, find the sum of sales per quarter, and display it in the last month of each quarter.Measure = VAR _sum_q = CALCULATE(SUM(financials[Sales]),FILTER(ALL('financials'),'financials'[Date].[Quarter] = MAX('financials'[Date].[Quarter]))) RETURN SWITCH(MAX('financials'[Date].[Month]), "March",_sum_q, "June",_sum_q, "September",_sum_q, "December",_sum_q, BLANK())2. Create a new measure, find half of all sales, and then display it in December.
Measure 2 = VAR _sum_y = CALCULATE(SUM(financials[Sales]),ALL('financials')) / 2 RETURN IF(MAX('financials'[Date].[Month]) = "December",_sum_y,BLANK())If your Current Period does not refer to this, please clarify in a follow-up reply.
Best Regards,
Clara Gong
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Hi pmargari ,
Since your screenshot doesn't show sales, it's not entirely clear to me how column E in your table is calculated. I did some calculations using my "financials" table and I hope this helps.
1. Create a measure, find the sum of sales per quarter, and display it in the last month of each quarter.Measure = VAR _sum_q = CALCULATE(SUM(financials[Sales]),FILTER(ALL('financials'),'financials'[Date].[Quarter] = MAX('financials'[Date].[Quarter]))) RETURN SWITCH(MAX('financials'[Date].[Month]), "March",_sum_q, "June",_sum_q, "September",_sum_q, "December",_sum_q, BLANK())2. Create a new measure, find half of all sales, and then display it in December.
Measure 2 = VAR _sum_y = CALCULATE(SUM(financials[Sales]),ALL('financials')) / 2 RETURN IF(MAX('financials'[Date].[Month]) = "December",_sum_y,BLANK())If your Current Period does not refer to this, please clarify in a follow-up reply.
Best Regards,
Clara Gong
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello Clara,
Million thanks for your help ,code and example it was perfect and as you can see already using it 😉
Din't put the sales as the column E was already the calculation from sales vs commission table , but you got what was needed.
What I was looking was a way to show a measure at quarter only or year !!Kind regards
Paulo
