How to add a column to show gaps between Business Plan and Actuals for each line type?

[NenadV]

Hi,

If I need to show Gap as the 3rd column, what function I need to use?

. . . . 

. .  . .

Thanks

[stevejoyce]

Gap as in difference?  You can reference the result of columns by expression name or column number.  So if "Business Plan" and "Actuals" are your expression labels, you can have a 3rd measure that is:

[Business Plan] - [Actuals]

[NenadV]

Thanks. I know that. 

In this instance, the data model consists of several dimensions with pairs of measures.

For example:

[stevejoyce]

In this case, you can create a new RecType for Gap.   

It doesn't look like RecordType is really associated to your data model since you have a different calculation per RecType.  So add a RecType =3 and you can have:

If(RecType = 1, sum(SP_Qty), If(RecType=2, sum(SalesQty), sum(SP_Qty) - sum(SalesQty)))

Or go to Presentation tab of your pivot table, select RecordType and choose Show Partial Sums.

Then your measures can be like below:

i'm assuming secondarydimensionality() in this case =1 for your new total column.  You can insert the function as a measure secondarydimensionality() to confirm.

If(seconarydimensionality() = 1,  sum(SP_Qty) - sum(Sales Qty), If(RecType = 1, sum(SP_Qty), sum(SalesQty))

Double check i closed out parenthesis correctly.

[NenadV · Accepted solution]

Steve, thanks for looking into this.

Yes, RecType is included into the data model. Neither of the two suggestions work.

It looks like I'll have to remodel the data model.