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] ) )
)

     

  • 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.