Dealing with Absolute Values

[bigck2]

Hello,

I'm using PowerPivot to store and score forecasts. I have a FactData table with a column that identifies each row as 'Actuals', 'Budget', or 'Rolling' which I am using for the forecasts.

I'm able to build PivotTables with calculated fields that compare 'Rolling Total Rev' to 'Actual Total Rev', but when I'm trying to score these I am having a hard time using absolute values. I'm interested in the mean absolute deviations and not just the deviations.

I tried using calculated fields like this:

Forecast Var Total Rev:=[Total Rev] - [Rolling Total Rev]

Forecast Abs Var Rev:=ABS([Forecast Var Total Rev])

When I then put Forecast Abs Var Rev, it works fine for each row, unless I try to group these by a field like Region or Regional Manager in a Pivot Table. It will then give me an absolute value but not the absolute value I'm looking for.

Any suggestions on how I might approach this problem?

Thanks,

Chris

 

[scottsen]

I suspect what is happening is your are getting the absolute value of the *aggregate* difference (which includes some some + and some neg)... when you really wanted the total of the absolute differences.

You are going to get that ABS down at a "lower" level -- but I guess that will depend on what your data looks like.

I can imagine a measure like... Total Diff := SUMX(Thingies, ABS([Total Rev] - [Rolling Total Rev]))

But, you will have to tell me what to put in "Thingies"

 

[bigck2]

Hey Scottsen,

You understand my issue perfectly. I have tried using a variation of that SumX formula like this:

=SUMX( FactData, ABS( [Forecast Var Total Rev] ) )

I also tried:

=SUMX ( FactData, ABS( [Rolling Total Rev] - [Total Rev] )

In both cases I get some strange results. It seems like I'm getting the sum of [Total Rev] + [Rolling Total Rev].

I'm not sure what you mean by "Thingies" lol. FactData is the table I have in the Data model.

 

[scottsen]

FactData, Thingies... same thing

So, your SUMX look totally reasonable to me. Walk each row in FactData, evaluate the ABS of the delta, ... and add those together for a total delta. Ya, that feels good to me.

If you can sanitize your data enough to share to me, I can take a look. Otherwise, we will have to dig into what you mean by "strange results"

 

[bigck2]

The thing I think that is throwing it off is the way my table is set up. I don't have a column for 'Total Rev' and then a column for 'Rolling Total Rev'. Those are calculated fields.

There is a column labeled: "Type" which has either: "Actuals", "Budget", or "Rolling".

So the SumX seems like it would work if it could go down each row and then do subtract [Total Rev] - [Rolling Total Rev].

In my data model, there are three rows for Jan 2015. One for "Actuals", "Budget", and one for "Rolling" all in the same table.

I was thinking I need to figure out how to make a table (with some DAX formulas) that has Total Rev for "Actuals" and "Rolling"

 

[scottsen]

It should be fine that they are measure, and as long as [Rolling Total Rev] and [Total Rev] correctly handle Actuals vs Budget vs Rolling... it should work todally fine.

Does your Total Rev look something like:
=CALCULATE(Facts[Amount], AmountType="Actual")
?

 

[bigck2]

These are the formulas I have for the measures:


Total Rev:=CALCULATE(
sum(FactData[Total Revenues] ),
FactData[Type] = "Actuals"
)


Rolling Total Rev:=CALCULATE(
sum(FactData[Total Revenues] ),
FactData[Type] = "Rolling"
)

 

[scottsen]

Okay, I think sumx across all rows in the fact table is probably not quite what we want. We need to somehow "tie" these rows together. There are probably other ways to do this, but I am thinking...
1) You could just add a calc column. Say, Budget rows... go find the Actual, do the compare, add the value in the calc column. A separate measure can sum those all up.
2) Well, kinda the same thing, but via measure -- the point would be you need something that ties these rows together. Maybe... it's just the date, which would work like:
Total Delta by Date := SUMX(VALUES(Calendar[Date]), ABS([Total Rev]-[Rolling Total Rev]))

As you have it now, you are basically doing ABS(blank - [Rolling Total Rev]) then adding ABS([Total Rev] - blank) and ending up w/ a big delta, when it should have been near zero if those were SUMX'd at the same time. You need to find the proper granularity to do that.

The calc column is likely easier, and if you don't have > 200k rows or so, I would just do that.

 

[bigck2]

Hey Scott,

Thanks a lot for your insight in to my problem. I have been able to find a solution that works, but it seems a little but unstable.


In the FactData table before updating I have columns: Date, Type, Property.


I added a column called "Lookup" which is defined as = =[@Property]&[@Type]&[@Date]

I then added a column called Total Rev Actuals defined as = =IF([@Type]="Rolling", IFERROR(INDEX(G:G, MATCH([@Property]&"Actuals"&[@Date],X:X, 0)),0),0)


Where G:G is a column for Total Rev and X:X is the new column "Lookup".

It is worth noting my data model is composed of three tables that "live in" the excel file itself in different worksheets. I have added these columns in the FactData table in that worksheet. I was not able to do this directly in the Data model as a calculated column. I don't think I can use the Index/Match functions in a calculated column. (I thought this was the whole point of PowerPivot was to avoid having to use Index/Match! Lol). I tried at first to add this in the Data model but those functions would not show up.

I don't think my solution would work if these tables "lived in" a database that I could not directly manipulate.

Finally, I added two calculated measures:

Forecast Total Rev Actuals:=CALCULATE( SUM( FactData[Total Rev Actuals] ),
FactData[Total Rev Actuals]<>0
)

Forecast Var Total Rev:=SUMX( FactData, ABS([Forecast Total Rev Actuals] - [Rolling Total Rev]))

Now when I put the measure "Forecast Var Total Rev" in a PivotTable it does provide what I am looking for when I group by Region or Regional Manager.

Still this whole methodology seems a little bit unstable to me. I'm going to need to carry this out for Total Exp, and NOI as well. It will probably work for my purposes because my main table FactData only has about 3,000 rows. I think by the end of the year I will probably only have 10,000 - 20,000 rows. I'm thinking there is a better way to approach this, that I will probably figure out in another couple of months. Lol.

Anyways, just wanted to share what I implemented. It works, and that's what matters right now. Thank you again for your help. I believe you have replied to some of my other posts, and I have always gained insight from your comments.

Thanks,

Chris