DAX: Summing of PRODUCTX results over categories

Question

I am working on a sales forecast table, which is based on the following logic: We have a complete sales table from 2019, where the products are categorized in clusters and if needed in subcategories inside the clusters for every country. (LUT_Sales19) We have another Excel table for every country and subcategory with yearly expected growth rates up until 2025. (I_Growth)

I want to calculate the expected sales amount for year x and category y by multiplying the respective sales figure from 2019 with the compounded growth rate. (E.g. if we have a sales number x for UK, for category 1 in 2019 and growth rates of 1%, 2% and 3% for 2020, 2021 and 2022 respectively, than the expected sales for 2022 is x * (1+1%) * (1+2%) * (1+3%).)

All the calculation should happen in PowerPivot with measures. So far I was able to get the 2019 sales figures as a base for the calculation for every year:

VAR Sales2019 =
          CALCULATE(SUM(LUT_Sales19[Net_Sales]), ALL(LUT_date_year[Year]))

And I could get the compound growth rates as well:

VAR CumGrowth =
          CALCULATE(PRODUCTX(I_Growth, 1+I_Growth[Growth]),
             FILTER (ALL ( LUT_date_year ) ,LUT_date_year[Year] +1<= MAX ( LUT_date_year[Year])))

The resulting pivot table is correct for the categories but the sum for the clusters and the grand total is off for every year other than 2019. This is the measure I use:

IF(MAX(LUT_date_year[Year])=2019,
   Sales2019, 
   CALCULATE(SUMX(LUT_Sales19, LUT_Sales19[Net_Sales]*CumGrowth), ALL(LUT_date_year[Year]))
   )

I thought, that using SUMX will fix the problem, but it persists, and I am running out of ideas.

What did I miss?

Thank you for the help in advance.

Peter Benkö Peter Benkö · Accepted Answer · 2020-09-03T07:18:15

After some research this post has helped me understand the problem: https://community.powerbi.com/t5/Desktop/DAX-sum-the-measure-values-in-total-and-Not-the-calculated/td-p/740509

It turns out, that I need two additional measures: one to SUMX the VALUES for the categories to get the Clusters calculate correctly, and another one to get the Grand Total by SUMX the VALUES of the Clusters. This third measure has the correct values for the categories, clusters and the Grand Total.

So, to calculate the values for the categories:

Sales_CAT:=VAR Sales2019 =
          CALCULATE(SUM(LUT_Sales19[Net_Sales]),ALL(LUT_date_year[Year]))

VAR CumGrowth = CALCULATE(PRODUCTX(I_Growth ,1+I_Growth[Growth]) , FILTER (ALL ( LUT_date_year ) ,LUT_date_year[Year] +1<= MAX ( LUT_date_year[Year]))) RETURN

IF(MAX(LUT_date_year[Year])=2019 , Sales2019 , Sales2019 * CumGrowth )

Then to get the sums for every cluster:

Sales_Cluster:=SUMX(VALUES(LUT_CAT_Cluster[Categories]), LUT_Sales19[Sales_CAT])

And finally to get the values for categories, clusters AND grand total:

Sales_GrandTotal:=SUMX(VALUES('LUT_Countries'[Country_ID]), LUT_Sales19[Sales_Cluster])

In the pivot table I have to use the Sales_GrandTotal measures, and all is fine.

DAX: Summing of PRODUCTX results over categories

1 Answers