SUMX AGGREGATION TOTALS WEIRD

[Anonymous]

Hi I force the summarization by column on a measure by item, the result is correct, but when I want to show the total something looks weird. 

Formula :

Comm Frcst Absolute Error =

IF('Measures IM'[Z Demand Item]<>0,

SUMX (

VALUES ( 'Item Area Values'[ITEM] ),

ABS('Measures IM'[Z ComFrcst Item]-'Measures IM'[Z Demand Item])

),

BLANK()

)

Result:

How I can fix this sum of totals?

Thanks a lot for your help.

[mwegener · Accepted solution]

Hi @Anonymous ,

if you want to have the sum of all date forecasts, you also have to iterate over them.

Try this:

Comm Frcst Absolute Error =
SUMX (
    'Bridge Dates',
    SUMX (
        VALUES ( 'Item Area Values'[ITEM] ),
        ABS ( [Z ComFrcst Item] - [Z Demand Item] )
    )
)

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

[Anonymous · Accepted solution]

Hi all,

I already find a way to solve:

Comm Frcst Absolute Error =

VAR TableAgg = SUMMARIZE('Date','Date'[Date Forecast],"Agg_Value",[Comm Frcst Absolute Error Base])

RETURN

IF(HASONEVALUE('Date'[Date Forecast]),[Comm Frcst Absolute Error Base],sumx(TableAgg,[Agg_Value]))

Put the result on a virtual table and then aggregate.

Thanks all of you for your help.

[mwegener]

Hi @Anonymous ,

I think your IF statement receives a different result on the total than on the monthly level.

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

[Anonymous]

Hi @mwegener , actually I try this alternative, but the result is the same.

[mwegener]

Hi @Anonymous ,

could you give an overview of your data model and the other two measures?

[Anonymous]

Hi @mwegener, yes, this is it:

Measures:

Z ComFrcst Item =

var Unit = CALCULATE(SUM('W_GROWTH_ITAR'[COMM_FRCST]), All(W_GROWTH_ITAR[ITEM]))

var Cost = CALCULATE(SUM('W_GROWTH_ITAR'[COMM_FRCST]), All(W_GROWTH_ITAR[ITEM]))*SUM('Item Area Values'[UNITCOST])

var Price = CALCULATE(SUM('W_GROWTH_ITAR'[COMM_FRCST]), All(W_GROWTH_ITAR[ITEM]))*SUM('Item Area Values'[LISTPRICE])

var SelectMeasure = MIN(UoM[ID])

return

if(

HASONEVALUE(UoM[Name]),

SWITCH(SelectMeasure,

1,Unit,2,Price,3,Cost))

Z Demand Item =
var Unit = CALCULATE(SUM('Demand Planning'[Demand]),ALL('Demand Planning'[Item]))
var Cost = CALCULATE(SUM('Demand Planning'[Demand]),ALL('Demand Planning'[Item]))*SUM('Item Area Values'[UNITCOST])
var Price = CALCULATE(SUM('Demand Planning'[Demand]),ALL('Demand Planning'[Item]))*SUM('Item Area Values'[LISTPRICE])
var SelectMeasure = MIN(UoM[ID])
return
if(
HASONEVALUE(UoM[Name]),
SWITCH(SelectMeasure,
1,Unit,2,Price,3,Cost))

Data model:

In Red: Qty tables with different level of aggregation

In Orange: Comom related Table

In Green: Date Table

The other measures are the result of the ABS Error formula.

[mwegener]

Hi @Anonymous ,

the data model and the measures look very complex. (Too complex for my taste.)

I think based on that I can hardly predict a result.

I would recommend a star schema and precalculations with Power Query (unit, cost, price)

https://docs.microsoft.com/en-us/power-bi/guidance/star-schema

[Anonymous]

Measures:

Z ComFrcst Item =

CALCULATE(SUM('W_GROWTH_ITAR'[COMM_FRCST]), All(W_GROWTH_ITAR[ITEM]))

Z Demand Item =
CALCULATE(SUM('Demand Planning'[Demand]),ALL('Demand Planning'[Item]))

Then I aggregate by item to calculate this:

SUMX (

VALUES ( 'Item Area Values'[ITEM] ),

ABS('Measures IM'[Z ComFrcst Item]-'Measures IM'[Z Demand Item])

)

the weird thing is the grand total doesn't summarize correctly.

Thanks.

[mwegener · Accepted solution]

Hi @Anonymous ,

if you want to have the sum of all date forecasts, you also have to iterate over them.

Try this:

Comm Frcst Absolute Error =
SUMX (
    'Bridge Dates',
    SUMX (
        VALUES ( 'Item Area Values'[ITEM] ),
        ABS ( [Z ComFrcst Item] - [Z Demand Item] )
    )
)

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

[Anonymous]

Hi @mwegener, actually works, I try it again.

SUMX (

VALUES ('Date'[Date Forecast]),

SUMX (

VALUES ( 'Item Area Values'[ITEM] ),

ABS ( 'Measures IM'[Z ComFrcst Item] - 'Measures IM'[Z Demand Item] )

)

) 

Thanks!!

[Anonymous]

Does not work, but help me to find the way, thanks a lot @mwegener