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 

 

Thanks for efforts. I tried but didn't work. Also, I have to do at item level and roll it up to product groups. I tried with below one after changing product group to item number in sumx

 

Measure =
SUMX (
    VALUES ( DimProduct[Item Number] ),
    ABS ( SUM ( SalesTable[Totalsales] ) - SUM ( FinalPredictions[Predictedsales] ) )
)