11 Replies Latest reply on Jul 19, 2019 10:25 AM by Hari Ankem

# Grand  Totals as Difference from 2 Dimension with different measure values on rows

See Screenshot below.

In the place of Grand Total I would like to see the difference between Reference Period and Selection Period. On every row is a different measure.

• ###### 1. Re: Grand  Totals as Difference from 2 Dimension with different measure values on rows

Instead of using the measures themselves, you can use a calculated field which shows the measure value when it shows the aggregate for the Selection Reference, but the diff for the grand total.

Below it shows with the SuperStore data source

Instead of using the Quantity, it uses this expression as the measure

IF COUNTD([Ship Mode]) = 1 THEN

// this is used when aggregating by Ship Mode

SUM([Quantity])

ELSE

// this is used in the Grand Total

SUM(IF [Ship Mode] = "Second Class" THEN [Quantity] END) - SUM(IF [Ship Mode] = "Standard Class" THEN [Quantity] END)

END

You will have to do this for each measure you display. In the screenshot below, I only did it for quantity

1 of 1 people found this helpful
• ###### 2. Re: Grand  Totals as Difference from 2 Dimension with different measure values on rows

Thank you Ewald.

• ###### 3. Re: Grand  Totals as Difference from 2 Dimension with different measure values on rows

Great solution, Hari!

One additional tweak: I suggest you right-click on 'Grand Total' and select 'Format'.

You can change the label from 'Grand Total' (which is no longer accurate) to 'Difference' or 'Delta'.

1 of 1 people found this helpful
• ###### 4. Re: Grand  Totals as Difference from 2 Dimension with different measure values on rows

Thanks Branden. Though that was not mentioned, it was being done.

3 of 3 people found this helpful
• ###### 5. Re: Grand  Totals as Difference from 2 Dimension with different measure values on rows

Thank You so much for the solution, Now How can I calculate the difference for an aggregated field? The way you have done for sum(quantity) in your calculation, I want to use and calculated field instead of sum.

1 of 1 people found this helpful
• ###### 6. Re: Grand  Totals as Difference from 2 Dimension with different measure values on rows

Please provide the packaged workbook (.twbx) so that any solution suggested will work for you.

• ###### 7. Re: Grand  Totals as Difference from 2 Dimension with different measure values on rows

How should I create delta in grand total using the above calculation, for "Profit Ratio" which is an aggregated field?

2 of 2 people found this helpful
• ###### 8. Re: Grand  Totals as Difference from 2 Dimension with different measure values on rows

Like this:

2 of 2 people found this helpful
• ###### 9. Re: Grand  Totals as Difference from 2 Dimension with different measure values on rows

Thank You So Much Hari, I was able to get difference through Grand total. Also, I have one more doubt, How can add % difference in the same table? So Now the table layout I want is Second Class, Standard Class, Difference and % Difference.

• ###### 10. Re: Grand  Totals as Difference from 2 Dimension with different measure values on rows

Hey Hari Can You help me with the second doubt? I want show difference as well as percentage difference in the same view. Thank You