cancel
Showing results for
Did you mean: 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: 1000Current month - previous year MTD sales: 150==> 15% saisononality percentageProduct result:Current month - current year MTD value: 28Forecast: 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 yearVAR 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 yearVAR 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_MeasureVAR AVG = Percentage_Measure // from aboveDIVIDE(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_MeasureVAR AVG = Percentage_Measure // from aboveSUMX(    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!  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.  