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. |
