Web Development

Performing SUM based on Group in Tableau

Today while working on one of my assignments I got a requirement to perform a SUM of one column values based on a Group of columns in Tableau and being a novice to Tableau I started searching on the internet to find a way to achieve it and after spending few hours I found a solution so thought of sharing over here how I did it.

Below is my data set and I want to calculate the Amount spent each Month and the Total Amount spent in all months, the Average spent, and the Percentage of the Amount spent on each category.

As a first step, I created a Calculated field and named it as AmountSpentPerMonth, and used a FIXED level of Detail Expressions with the following expression:

{FIXED [Name],[Month]:SUM([Amount])}

Next, I created another Calculated field and named it as TotalSpentForAllMonths, and used a FIXED level of Detail Expressions with the following expression:

{FIXED [Name]:SUM([AmountSpentPerMonth])}

Then I created another Calculated field and named it as AverageSpent, and used a FIXED level of Detail Expressions with the following expression:

SUM([TotalSpentForAllMonths])/TOTAL(COUNTD([Month]))

Then I created another Calculated field and named it as PercentageSpentOnEachCategory, and used a FIXED level of Detail Expressions with the following expression:

[Amount]

Moved all calculated fields on Sheet together with other data fields and changed Percentage Compute Using “Category” as shown in a screenshot below:

Published on Java Code Geeks with permission by Arpit Aggarwal, partner at our JCG program. See the original article here: Performing SUM based on Group in Tableau

Opinions expressed by Java Code Geeks contributors are their own.

Arpit Aggarwal

Arpit is a Consultant at Xebia India. He has been designing and building J2EE applications since more than 6 years. He is fond of Object Oriented and lover of Functional programming. You can read more of his writings at aggarwalarpit.wordpress.com
Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Back to top button