When doing Calculations in Tableau Desktop, one major concept to keep in mind is the level of aggregation the calculation is being done. For example, there is a big difference between sum(sales/profit) vs. sum(sales)/sum(profit). If my data looks something like this
sum(sales) / sum(profit) =(10+5) / (20+2) = 15/22 = .68
sum(sales/profit) =10/20 + 5/2 = .5 + 2.5 = 3
From the explanation, it looks like sum(sales/profit) is something you might be looking for.
For more information about aggregations, please look at the links below.
If this does not resolve the issue, please attach a sample workbook with an explanation of desired result including what number is being shown and what number is desired.
that's helpful. That being said, leveraging your example - i'd actually want to be adding a third column on the right which would be showing 20/10 = 2 in row 1 and 5/2 = 2.5 in row 2. Do you have a recommended approach / calculation accordingly?
There are a couple of things I would like to clarify.
1. In the last comment, you said that row 1 should be 20/10. Did you mean 10/20 = .5? 20/10 would be profit/sales not sales/profit.
2. Tableau Desktop is created as a visualization tool and not a database. Tableau Desktop does not have the ability to manipulate the data within the database.
3. That being said, Tableau Desktop does have a separate table that it creates.
3. When creating a calculated field, Tableau Desktop will add a new column to Tableau's table.
4. It is possible to see Table's table by looking at the underlying data (please look at the following link View Data )
5. By adding the calculated filed sum(sales/profit), this should add the a new column in Tableau's table where row 1 = .5 (10/20) and row 2 = 2.5 (5/2)
Please look at the attached workbook.
I hope the clarifications helps. Please respond if there are any further questions.
Sales and Profit Sample.twbx 11.0 KB
I still think you aren't fully getting the challenge.
Let's look back at my example...
Currently I have 3 columns:
Instructor name | Attendance Count | Number Of Classes
- Instructor name is a fact
- Attendance count is a measure constructed by creating a 'count' in the underlying table
- Distinct Class Count is a measure constructed by created a 'count distinct' in the underlying table
I then created two calculations:
Calc1: [Attendance Count]/[Distinct Class Count]
Calc 2: sum([Attendance Count]/[Distinct Class Count])
See image below... but:
1. The values returned for both are the same in every row
2. If we look at row 2 (Mark) -- you;ll see attendance of 1796 - with Class count of 543 (red)
--- Calc 1 and Calc 2: Show teh value of 3
--- I am expecting 1796/543 = 3.31 -- instead I am getting 2.9144
3. If we look at the blue row - i'd expect to see 18/7 = 2.57 -- instead we get 0.0323
Looking at the screen shots, it seems that the calculation should the following.
sum([Attendance Count])/sum([Distinct Class Count])
It is very difficult for me to further assist without a workbook. Would it be possible to post a workbook?
Please note that Calculation 1 and calculation 2 will result in the same result.
In Tableau it is possible to assign an aggregation within a calculation. If an aggregation is not assigned, it is possible to change the aggregation of the calculation by simply right clicking the calculation pill. The following calculation: [Attendance Count]) / [Distinct Class Count] is the same thing as sum([Attendance Count]) / [Distinct Class Count]) when the aggregation is set to sum. It is possible to change the aggregation (min,max,average, etc.) for calculation 1 but it is not possible to change to change the aggregation for calculation 2 because the aggregation of "sum" has already been defined within calculation 2.
I hope that this gives a little more clarification.
I figured it out - the correct equation was COUNT([Check In Count])/COUNTD([Distinct Class Count])
Thanks for your help
I am happy to hear that the issue was resolved. If possible, please mark this question as resolved.
Hope you have a great day.