How to Add a Delta Column in Pivot Table for Comparing Alternate Dimensions

[Pico]

Hi there, I have an inverted pivot: rows are some measures and the column are dimension (*see later).

We are using alternate dimension for column: the field is a DATE, and we are using the pivot as comparison between two snap dates, that are Alternate dimensions (Snap_date_1 and Snap_date_2).

the column formula that shows only the alternate values for the snap date works fine and is :

IF( [Date]= Snap_date_1 , Snap_date_1 ,
IF( [Date]= Snap_date_2, Snap_date_2 ))

This pivot is shown only is we have both alternate dimension valid.

We would like to have a third column (aka Delta) that shows the difference between the measures of snap 1 and snap 2.

Example:

measures	snap date 1	snap date 2	Delta
total cost	100	120	20
total usage	55	65	10
number of account	10	12	2

Is there a way to acheive this? 

Thank you very much

Pico

[piotr-bratek]

Hi,

can you attach a sample dataset with couple of rows so that it's easier to reply

cheers
P

[Pico]

Subject: Clarification on Data and Pivot Chart Settings

Sorry, all the data is sensitive. I'll try to give you as much information as I can:

The field [DATE] is a standard date. The alternative values are two dates selected by the user in the relevant filter panes.

In the pivot chart, these are the settings:

Dimensions:
Rows: Values
Columns: Dates (as mentioned previously)
Measures:

• The first value is calculated as:

SUM( {<Area = 'Specific Area'>} Budget)

• The second value is calculated as:

SUM( {<Area = 'Specific Area', Business_Owner_group = {'Specific Value'}>} Budget)

• The third value is:

[Second Value] / [First Value]

, expressed as a percentage.

We actually have more rows, but they are variations of the three above. If you need further details, feel free to ask, and I'll do my best to provide them.

Thank you.

[piotr-bratek]

OK, that's better... but it's not all.

We don't need real data but at least the table structure is always appreciated 😉

You can export your table with relevant columns from your sheet (so that we can see real columns and structure) and add 5-10 dummy rows (change customer names to C1, C2, real valus to 10,20,30).

It saves time and we don't have to guess how it looks.
And finally you receive a solution relevant for your data.

[Pico]

Sorry it's not clear to me what do you mean with export your table (pivot chart? what is a table?) with relevant columns from your sheet.
Here is the .xls file downloaded and trimmed
and
the model, where yellow is DATE field, and blue some filters used in the set analysis

[piotr-bratek · Accepted solution]

Hi Pico,

I still have too little information about your data, but having that I would probably use other approach - focused on measures, not a dimension. One measure for first, selected date, second for the other one.

I don't know what's the "standard date" and what's the relation between snap (and what that is) and "standard". Yet I believe, whatever that is the following approach should work:

[piotr-bratek]

Then when you have more measures, you can apply:
# ValueList function to list all your measures
# and column(2) - column(1) measure as DELTA

[Pico]

"Standard Date" (=field [Budget Snap Date]) is the pool from which the user can select two data (Snap 1 and Snap 2, the Alternate states) in order to compare them.

Sadly we are forced to have, as column, the two snap date (and hopefully the Delta), and the measures as rows. 
Your approach is interesting, I don't understand how to use it with the different rows (ValueList works with static values, I have measures )
It seems to me that your SD1&2 force one precise measure ([% IT])

[piotr-bratek]

If you select snap1 & 2 from the Date column I guess you will have two values selected so min(Date) is the 1st one, max(Date) - the othere one.

You use ValueList exactly as explained in Help - for both: your dimension and measure columns.

In the 1st measure coulumn you will have Total Cost Snap1, Total Usage Snap1. 
While in the 2nd measure you will have Total Cost Snap 2, Total Usage Snap2. 

The rest is DELTA which is purely column(2) - column(1)

The last thing you will use MEASURE LABEL for Snap Date 1, and Snap Date 2.

and then you got it 🙂

[Pico]

Thank you, it works nearly as expected! Last question: I have both money-formatted data and %-formatted ones as in the excel file).

I had the formatting in every measure as desired, set the "number formatting" as "measure expression" but it doesn't work.
I cannot either make the NUM() function work in the nested IF measure, like
IF( ValueList('Number of Orders', 'Average Order Size', 'Total Amount') = 'Number of Orders', NUM(count(SaleID),'0.0', '.'). 
How can I have both € and % in the same column?

Thank you again