cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Fuhrer786
Frequent Visitor

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 level  but right now it sums up and then does abs diff.I saw couple of links but in my case data is coming from 3 tables not one.

 

Sample Data

 

                        Total Sales   Fcst    Expected(Abs diff)   My output

Product Group       300        270               90                   30

Item1                     100         50                 50                   50

Item2                     200         240               40                   40

 

Data is coming from 3 tables- Product info from DimProduct, Sales from Salestable and Forecast from FinalPredictions  table all joined by ItemNumber which is common join between tables

 

My measure:

ABS(Sum(SalesTable[Totalsales])-sum(FinalPredictions[Predictedsales]))
 
Any help appreciated! Thanks
2 ACCEPTED SOLUTIONS
jdbuchanan71
Super User II
Super User II

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

View solution in original post

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.

View solution in original post

4 REPLIES 4
jdbuchanan71
Super User II
Super User II

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

View solution in original post

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

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.

View solution in original post

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

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors