Forum Discussion

Fuhrer786's avatar
Fuhrer786
Frequent Visitor
6 years ago
Solved

Roll up Abs difference at group level

Hello All,   I am calculating abs differnce between actual and fcst at SKU level which rollups upto different product groups. I want to  roll up abs diff  of all items  correctly at product group l...
Need Help
  • jdbuchanan71's avatar
    jdbuchanan71
    6 years ago

    Hello Fuhrer786 

    Give this a try.  It will generate a virtual table of all the [Product Group] in your DimProduct table then, for each row in the virtual table it will do the ABS calc and finally it will add up all those values.

    Measure =
SUMX (
    VALUES ( DimProduct[Product Group] ),
    ABS ( CALCULATE ( SUM ( SalesTable[Totalsales] ) ) - CALCULATE ( SUM ( FinalPredictions[Predictedsales] ) ) )
)
  • jdbuchanan71's avatar
    jdbuchanan71
    6 years ago

    Hello Fuhrer786 

    No, you are not doing anything wrong.  I forgot to force the context transition in the iterator.  It should have been.

    Measure =
SUMX (
    VALUES ( DimProduct[Product Group] ),
    ABS ( CALCULATE ( SUM ( SalesTable[Totalsales] ) ) - CALCULATE ( SUM ( FinalPredictions[Predictedsales] ) ) )
)

    But two measures is the way to do it because they force the context transition and can be used in other places.

jdbuchanan71's avatar
jdbuchanan71
Super User
6 years ago

Hello Fuhrer786 

Give this a try.  It will generate a virtual table of all the [Product Group] in your DimProduct table then, for each row in the virtual table it will do the ABS calc and finally it will add up all those values.

Measure =
SUMX (
    VALUES ( DimProduct[Product Group] ),
    ABS ( CALCULATE ( SUM ( SalesTable[Totalsales] ) ) - CALCULATE ( SUM ( FinalPredictions[Predictedsales] ) ) )
)
Fuhrer786's avatar
Fuhrer786
Frequent Visitor
6 years ago

Hello jdbuchanan71 

 

It worked perfect now. I created 2 separate measures for actualsales and forecast value

 

TotalSales=sum(Salestable[TotalSales])

TotalForecast=sum(Finalpredictions[PredictedSales])

 

Measure =
SUMX (
    VALUES ( DimProduct[Item Number] ),
    ABS (TotalSales - TotalForecast )
)

 Formula still same, I must be doing something wrong but creating extra measures worked for me! Thank u so much

  • jdbuchanan71's avatar
    jdbuchanan71
    Super User
    6 years ago

    Hello Fuhrer786 

    No, you are not doing anything wrong.  I forgot to force the context transition in the iterator.  It should have been.

    Measure =
SUMX (
    VALUES ( DimProduct[Product Group] ),
    ABS ( CALCULATE ( SUM ( SalesTable[Totalsales] ) ) - CALCULATE ( SUM ( FinalPredictions[Predictedsales] ) ) )
)

    But two measures is the way to do it because they force the context transition and can be used in other places.