Forum Discussion
Commissions balance report
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.
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
