Adding calculated fields to Pivot Table

[#1]

For the object Product Line Total, I would like to calculate the variance between the months for the value Extension for both "R" and "F", but am struggling to figure it out.  I have attached the file.  As always any assistance is greatly appreciated.

[#2]

Assuming you always want [Product Type] R and F, and the variance between them:

1. Drop the dimension [Product Type]

2. Create three expressions and be explicit using SET analysis to get what you need.

Expression for R:

=sum

(

    {<

        [Product Type] = {'R'}

    >}

Extension

)

Expression for F:

=sum

(

    {<

        [Product Type] = {'F'}

    >}

Extension

)

And, the variance:

=sum

(

    {<

        [Product Type] = {'R'}

    >}

Extension

)

-

sum

(

    {<

        [Product Type] = {'F'}

    >}

Extension

)

....

That is just one way.......

Hope it helps.

[#3]

Sam,

Thanks for the feedback, but I would like to get the variance of "F" between the month 1 and month 2, and of "R" between month 1 and month 2 not the difference betweet F and R.

[#4]

So, you want R vs. prior month, F vs. prior month?

There are a few ways, but let's take a direct approach.

Modify your load script such that you have, on every row, a [Prior Month]

That is done via addmonths([Month],-1) AS [Prior Month]

Then, to get the prior month value of F, in your expression, use something like this:

=sum

(

    {<

        [Product Type] = {'F'},

        Month = [Prior Month]

    >}

Extension

)