cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Axel_hnk
Frequent Visitor

SUMX Problem with Forecast % on Certain Level

Hi there,
I basically want to calculate a forecast for a current MTD Value for a product till the end of the month. The problem: Although I am using SUMX / VALUES the aggregation/totals are not considering that.

Due to uncertainties regarding past availablity I want to calculate the saisonality for the forecast on the combined level of Product_Category AND Product_Subcategory. A Product_Subcategory can be applied to multiple Product_Categories.

Example:
Product_Category_Product_Subcategory result:
Current month - previous year total sales: 1000
Current month - previous year MTD sales: 150
==> 15% saisononality percentage

Product result:
Current month - current year MTD value: 28
Forecast: 28 / 0.15 = 186.66

 

The forecast value is derived from a 3 year average of the previous years. The example shows the 1 year previous example calculation.

Percentage_Measure =
VAR MaxDate = MAX ( Date_Dax[Date] )
VAR EndOfMonthCurrent = MAX ( Date_Dax[Calendar EndOfMonth] )

VAR StartOfMonthPast1 = DATE ( YEAR ( MaxDate ) - 1, MONTH ( MaxDate ), 1 )
VAR EndOfMonthPast1 = EOMONTH ( StartOfMonthPast1, 0 )

VAR EndOfDatePast1 =
IF (
//Check if endofmonth selection - relevant for Feb 29/28
MaxDate = EndOfMonthCurrent,
//Calc the real end of month of the past year
EOMONTH (
DATE ( YEAR ( MaxDate ) - 1, MONTH ( MaxDate ), 1 ),
0
),
//use the given date
DATE ( YEAR ( MaxDate ) - 1, MONTH ( MaxDate ), DAY ( MaxDate ) )
)

//calculate the result for the whole month of the previous year
VAR VolumeEomPast1 =
CALCULATE (
SUM ( 'Sales'[Sales Value] ),
Date_Dax[Date] >= StartOfMonthPast1 && Date_Dax[Date] <= EndOfMonthPast1,
ALLEXCEPT (
Product_Table,
Product_Table[Product_Category_Product_Subcategory]
)
)

//calculate the result for the MTD value of the month of the previous year
VAR VolumeMtdPast1 =
CALCULATE (
SUM ( 'Sales'[Sales Value] ),
Date_Dax[Date] >= StartOfMonthPast1 && Date_Dax[Date] <= EndOfDatePast1,
ALLEXCEPT (
Product_Table,
Product_Table[Product_Category_Product_Subcategory]
)
)

// calculate the % - in our example 15%
VAR VolumePercent1 = DIVIDE (VolumeMtdPast1, VolumeEomPast1)

Here is what I'd like to see (forecast calculated on Category_Subcategory and then summed up to Category) :
(The table looks a bit weird - the community forum of MS makes my tables always weird, although generated with the basic table tool)

Product CategoryProduct_Category& SubcategoryProductForecastSales ValuePercentage
Cat_A  112517516,5%
 Cat_ASubCat_A 100015015%
  Prod_1333,335015%
  Prod_25007515%
  Prod_3166,662515%
 Cat_ASubCat_B 1252520%
  Prod_4501020%
  Prod_5751520%
Cat_B  5009520%
 Cat_BSubCat_A 3004515%
  Prod_6133,332015%
  Prod_7166,662515%
 Cat_BSubCat_C 2005025%
  Prod_82005025%

 

When I apply the follwing formula:

Forecast_Measure =
VAR VolumeAmount = Sales_Volume_MTD_Measure
VAR AVG = Percentage_Measure // from above

DIVIDE(VolumeAmount, AVG)
I get this (not really astonishing) - calculation on top level with aggregated values - WRONG:
Product CategoryProduct_Category& SubcategoryProductForecastSales ValuePercentage
Cat_A  1060,617516,5%
 Cat_ASubCat_A 100015015%
  Prod_1333,335015%
  Prod_25007515%
  Prod_3166,662515%
 Cat_ASubCat_B 1252520%
  Prod_4501020%
  Prod_5751520%
Cat_B  4759520%
 Cat_BSubCat_A 3004515%
  Prod_6133,332015%
  Prod_7166,662515%
 Cat_BSubCat_C 2005025%
  Prod_82005025%
 

So I need to use SUMX. I tried this:

VAR VolumeAmount = Sales_Volume_MTD_Measure
VAR AVG = Percentage_Measure // from above

SUMX(
VALUES(Product_Table[Product_Category_Product_Subcategory]),
DIVIDE(VolumeAmount, AVG)
)
But I got a very wrong result on the ALL_Category level (roughly something like the forecast sum fo all Subcategories multiplied by the number (count) of existing (even without Sales_Value) Subcategories. When I filter out a blank Subcategory the result changes as well (decrease).
So I experimented with dozens of combination leading to either one of the results. It's like as SUMX is not doing it's job properly.
I hope I could make my situation clear and you guys have an idea where I am wrong.
Much appreciated, thanks!
1 REPLY 1
v-kkf-msft
Community Support
Community Support

Hi @Axel_hnk ,

 

Please try the following generic measures for calculating total value.

 

Measure = 
SUMX ( VALUES ( 'Product_Table'[Product_Category& Subcategory] ), [Forecast_Measure] )

Or

Measure =
SUMX (
    SUMMARIZE (
        'Product_Table',
        'Product_Table'[Product Category],
        'Product_Table'[Product_Category& Subcategory],
        "_Forecast", [Forecast_Measure]
    ),
    [_Forecast]
)

 

 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Difinity Conference

Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.