Weighted Sales Performance Metric
I am trying to create a query that returns the best and worst performing products for a given customer, based on this year's net sales versus last year's net sales, weighted by the total net sales for all of the products sold to this customer in the last two years.
I have created Last Year Net Sales (up to the last month end): AGGREGATE(PERIODSTODATE([Invoice Date].[H1].[YEAR],[Invoice Date].[H1].[Month].[NOW-13]),measures.[Net Sales])
This year Net Sales: AGGREGATE(PERIODSTODATE([Invoice Date].[H1].[YEAR],[Invoice Date].[H1].[Month].[NOW-1]),measures.[Net Sales])
Percent change: ((AGGREGATE(PERIODSTODATE([Invoice Date].[H1].[YEAR],[Invoice Date].[H1].[Month].[NOW-1]),measures.[Net Sales]) - AGGREGATE(PERIODSTODATE([Invoice Date].[H1].[YEAR],[Invoice Date].[H1].[Month].[NOW-13]),measures.[Net Sales])) /AGGREGATE(PERIODSTODATE([Invoice Date].[H1].[YEAR],[Invoice Date].[H1].[Month].[NOW-13]),measures.[Net Sales])) * 100
Along with the product#, this gives me Four columns. In this example,
Product # Last Year to Date Net Sales This Year to Date Net Sales % Change
A 763.75 5.60 (99.27)
B 192,678.05 10,179.27 (94.72)
C 1,070.85 315.25 (70.56)
D 646.10 197.10 (69.49)
E 121.10 430.55 249.76
F 24,258.17 98,987.80 299.81
etc.
Obviously, although product 'A' has experienced a bigger loss of sales, year over year as a percentage, the sales drop for product 'B' is much more concerning, since it represents the bulk of all declining sales for this customer. Similarly, Product F's increase in sales is far, far ,more important than product 'E's. I'd like to show the 20 worst performing products, year over year, for this customer, and the 20 best. Once I completed this, I'd want to take this logic and create another query that would show the 20 worst performing customers, and 20 best, and also, a 3rd query that would show the best and worst performing products at the entire company level.
But for now, I want to merely weight the sales change of each product I've sold this customer based on the total sales for this product, for this customer, against the total sales of all products, for this customer.
So, I want to add a fifth column. It takes the total sales for this product for both years on each row, divides by the grand total sales for all products for those to years (329,653.59) and then multiplies by the percent change, which I think will give me a useful weighting (I am open to other suggestions).
Formatted to round, this gives us:
Product # Last Year to Date Net Sales This Year to Date Net Sales % Change Weighted (rounded)
A 763.75 5.60 (99.27) (0.23)
B 192,678.05 10,179.27 (94.72) (58.29)
C 1,070.85 315.25 (70.56) (0.30)
D 646.10 197.10 (69.49) (0.18)
E 121.10 430.55 249.76 0.42
F 24,258.17 98,987.80 299.81 112.1
so:
1) How do I do this? I was trying to do the first part: to get total net sales for all products for the last 2 years by creating a calculated measure that does this:
AGGREGATE(%TIMERANGE([Invoice Date].[H1].[Month].&[NOW-13],[Invoice Date].[H1].[Month].&[NOW-1]),MEASURES.[Net Sales])
I wanted it to jump up my product hierarchy from this particular product to my all products level and sum the sales, but it's not working - it's returning numbers that make no sense to me..
2) The percent formula essentially replicates the aggregation math of the other two formulas, which means we're doing the work three times. With the addition of this new column, that'd be four times. Is there any way to do each calculation once and then use that result for the next calculation to increase efficiency, because this is a very expensive query:
WITH MEMBER [Invoice DATE].[LYPTD] AS 'AGGREGATE(PERIODSTODATE([Invoice Date].[H1].[YEAR],[Invoice Date].[H1].[Month].[NOW-13]))' MEMBER [Invoice DATE].[PTD] AS 'AGGREGATE(PERIODSTODATE([Invoice Date].[H1].[YEAR],[Invoice Date].[H1].[Month].[NOW-1]))' MEMBER [MEASURES].[TYPTDM] AS 'AGGREGATE(PERIODSTODATE([Invoice Date].[H1].[YEAR],[Invoice Date].[H1].[Month].[NOW-1]),measures.[Net Sales])' MEMBER [MEASURES].[LYPTDM] AS 'AGGREGATE(PERIODSTODATE([Invoice Date].[H1].[YEAR],[Invoice Date].[H1].[Month].[NOW-13]),measures.[Net Sales])' MEMBER [MEASURES].[PTDMPCT] AS '((AGGREGATE(PERIODSTODATE([Invoice Date].[H1].[YEAR],[Invoice Date].[H1].[Month].[NOW-1]),measures.[Net Sales]) - AGGREGATE(PERIODSTODATE([Invoice Date].[H1].[YEAR],[Invoice Date].[H1].[Month].[NOW-13]),measures.[Net Sales])) /AGGREGATE(PERIODSTODATE([Invoice Date].[H1].[YEAR],[Invoice Date].[H1].[Month].[NOW-13]),measures.[Net Sales])) * 100' MEMBER [MEASURES].[PTDMDELTA] AS '(AGGREGATE(PERIODSTODATE([Invoice Date].[H1].[YEAR],[Invoice Date].[H1].[Month].[NOW-1]),measures.[Net Sales]) - AGGREGATE(PERIODSTODATE([Invoice Date].[H1].[YEAR],[Invoice Date].[H1].[Month].[NOW-13]),measures.[Net Sales])) ' MEMBER [MEASURES].[SamtestWeight] AS '(((AGGREGATE(PERIODSTODATE([Invoice Date].[H1].[YEAR],[Invoice Date].[H1].[Month].[NOW-1]),measures.[Net Sales]) - AGGREGATE(PERIODSTODATE([Invoice Date].[H1].[YEAR],[Invoice Date].[H1].[Month].[NOW-13]),measures.[Net Sales])) /AGGREGATE(PERIODSTODATE([Invoice Date].[H1].[YEAR],[Invoice Date].[H1].[Month].[NOW-13]),measures.[Net Sales])) * 100) * (AGGREGATE(PERIODSTODATE([Invoice Date].[H1].[YEAR],[Invoice Date].[H1].[Month].[NOW-1]),measures.[Net Sales]) + AGGREGATE(PERIODSTODATE([Invoice Date].[H1].[YEAR],[Invoice Date].[H1].[Month].[NOW-1]),measures.[Net Sales]))' MEMBER [MEASURES].[All_Prod_Net_Sales] AS 'AGGREGATE(%TIMERANGE([Invoice Date].[H1].[Month].&[NOW-13],[Invoice Date].[H1].[Month].&[NOW-1]),MEASURES.[Net Sales])' SELECT NON EMPTY {%label([MEASURES].[LYPTDM],"Last Year to Date Net Sales",""), %label([MEASURES].[TYPTDM],"This Year to Date Net Sales",""), [MEASURES].[PTDMPCT], [MEASURES].[ALL_PROD_NET_SALES]} ON 0, NON EMPTY order([Cust].[H1].[Customer].MEMBERS,measures.[PTDMPCT],BASC) ON 1 FROM [INVOICESFLAT]
Thanks in advance!