Hi,
I just wanted to say that I found my solution a while back and just wanted to post my solution here in case a novice in the future wants to know how to do it. Before you read any further, it's good to know that the calculation for Gross Margin wasn't done in the script, but in the front end.
Total Gross Margin % (for all years) is:
Sum([Gross Profit])/Sum(Sales).
And for the Gross Margin % this year and last year, I used a set analys of course. Take a look down below!
The expression for Gross Margin % this year is :
sum({<Year = {'$(=Max(Year))'}>}[Gross Profit]) / sum({<Year = {'$(=Max(Year))'}>} Sales).
The expression for Gross Margin % Last year is :
sum({<Year = {'$(=Max(Year) -1)'}>}[Gross Profit]) / sum({<Year = {'$(=Max(Year) -1)'}>} Sales).
The expression for the variance between those two is:
sum({<Year = {'$(=Max(Year))'}>}[Gross Profit]) / sum({<Year = {'$(=Max(Year))'}>} Sales)
-
sum({<Year = {'$(=Max(Year) -1)'}>}[Gross Profit]) / sum({<Year = {'$(=Max(Year) -1)'}>} Sales)
Remember that this exact script works IF the Gross Margin isn't calculated in the script, but in the front end. That's why the set analys is used on both aggregations. If Gross Margin % is calculated in the script, the expression would've looked something like this: Sum({<Year = {'$(=Max(Year))'}>} [Gross Margin]), which is for this year.
Okay, have a nice weekend!
Sincerely,
Betty Habtemariam
.png "Luminary Alumni"){kind=icon}
