11 Replies Latest reply on Aug 2, 2018 10:23 AM by Andrew Lines

# Two Calculation Methods

Hi Everyone

Hope you can help with an issue I'm having with calculations in Tableau. I have sales for two countries over the year.  My First calculation I want to do is a running sum of each country over the months, so a running_sum across.  The second calculation is a variance between the two countries for each month displayed in the total row.  I can get each calculation to work individually but not together.

Thanks Andrew

My raw data is structured like this

 Country Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec UK 5 0 3 0 0 6 0 8 0 9 0 0 US 5 6 0 6 3 3 0 2 2 0 0 0

What I would like to get to is this.  Running sum across and variance down

 Country Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec UK 5 5 8 8 8 14 14 22 22 31 31 31 US 5 11 11 17 17 20 23 23 25 27 27 27 Variance 0 -6 -3 -9 -9 -6 -9 -1 -3 4 4 4
• ###### 1. Re: Two Calculation Methods

Hi,

You need to work on your data structure of your source. Just pivot to get the month fields from columns to rows. Like this

Then create few calculated field to get the expected output

UK:

RUNNING_SUM(SUM({FIXED [Month]:SUM(IIF([Country]='UK',[Value],NULL))}))

US:

RUNNING_SUM(SUM({FIXED [Month]:SUM(IIF([Country]='US',[Value],NULL))}))

Variance:

[UK]-[US]

Once done then drag the fields as per below screenshot.

Workbook attached for your reference. Let us know if this help.

Mahfooj

1 of 1 people found this helpful
• ###### 2. Re: Two Calculation Methods

Fantastic Thanks.  Being just an example what I didn't explain was the countries can be any two chosen countries and there may be more dimensional information on the rows ?

• ###### 3. Re: Two Calculation Methods

Hi,

One quick question, do you want to create a cross-tab report or any visualization out it.

If you're going to create a graph to show both running sum and variance then here is my appraoch

And if you're looking for only cross tab report then you can have this

I guess you should go for trend where you can have a clear view of both measures.

workbook attached for your reference. Let us know if this help.

Mahfooj

• ###### 4. Re: Two Calculation Methods

you need to do some changes like this. Change the mark of Measure Names from color to detail and sort the color legend. Put US on top to get the expected variance. Follow the screenshots.

Same for cross tab. sort the country like this

Let us know if this help.

Mahfooj

• ###### 5. Re: Two Calculation Methods

Hi Mahfooj

Thanks so much for your help on this.  Its the pivot I'm after.  Guess the last part would be bringing the word variance back in ?

• ###### 6. Re: Two Calculation Methods

Here you go,

Once you connect your data source, using shift key you can select all the month fields and choose Pivot option to transpose the data.

Once done you'll have some this like this. Rename the Pivoted fields as per requirement and delete the unwanted fields.

Hope this will help.

Mahfooj

• ###### 7. Re: Two Calculation Methods

The Transposing of the data is fine.  I just need to get the word Variance back in on the Total row.  LIke you had on the first screen shot you sent

Thanks

• ###### 8. Re: Two Calculation Methods

Hi,

This is what max we can achieve. Else you need to create separate sheet for variance. You need to understand your country values are coming from a dimension and Variance is a calculated measure which can not come under country field. Hope you get my point.

Let us know if you've any concern.

Mahfooj

• ###### 9. Re: Two Calculation Methods

Thanks Mahfooj.  This has helped a lot just a shame we can't push Tableau to fulfill the whole requirement.  I was hoping I could use your running Sum logic as above and put the variance into the subtotal row using the

'if First = 0 and Last = 0 then ...........' syntax

• ###### 10. Re: Two Calculation Methods

Tableau doesn't recommend for any cross-tab report. Its not a good practice. Better if we can create visual from the data to get insight.

However, you may close the thread by marking my answer as correct if my solution helped you in any ways to get some output so that it gets hide from unanswered list.

Mahfooj

• ###### 11. Re: Two Calculation Methods

Hi Mahfooj

Hope you're able to help again on this issue.  Our users preferred the first solution having a measure for UK, US and Variance.  Though I made it slightly more dynamic by calling UK (Primary Country) and US (Secondary Country) now passing a Parameter into each.  Though we have action based upon these values to a more detailed report therefore need to pass the value of i.e. UK into action.  So is there anyway to count the measure names as in the image below in red for Primary Country, Secondary Country and Variance ?

Thanks again and appreciate your help.  I can create another request if you prefer to answer on that.

Andrew