Commissions balance report

[pmargari]

 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

[Anonymous · Accepted solution]

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.

[pmargari]

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