SUMX Problem with Forecast % on Certain Level

[Axel_hnk]

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 Category	Product_Category& Subcategory	Product	Forecast	Sales Value	Percentage
Cat_A			1125	175	16,5%
	Cat_ASubCat_A		1000	150	15%
		Prod_1	333,33	50	15%
		Prod_2	500	75	15%
		Prod_3	166,66	25	15%
	Cat_ASubCat_B		125	25	20%
		Prod_4	50	10	20%
		Prod_5	75	15	20%
Cat_B			500	95	20%
	Cat_BSubCat_A		300	45	15%
		Prod_6	133,33	20	15%
		Prod_7	166,66	25	15%
	Cat_BSubCat_C		200	50	25%
		Prod_8	200	50	25%

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 Category	Product_Category& Subcategory	Product	Forecast	Sales Value	Percentage
Cat_A			1060,6	175	16,5%
	Cat_ASubCat_A		1000	150	15%
		Prod_1	333,33	50	15%
		Prod_2	500	75	15%
		Prod_3	166,66	25	15%
	Cat_ASubCat_B		125	25	20%
		Prod_4	50	10	20%
		Prod_5	75	15	20%
Cat_B			475	95	20%
	Cat_BSubCat_A		300	45	15%
		Prod_6	133,33	20	15%
		Prod_7	166,66	25	15%
	Cat_BSubCat_C		200	50	25%
		Prod_8	200	50	25%

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!

[v-kkf-msft]

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.