KNOWLEDGE BASE

How To Calculate Sum Based On The Result of An Aggregated Data Field

Published: 03 Dec 2021

Question

How To Calculate Sum Based On The Result of An Aggregated Data Field?

Environment

• Tableau Desktop

To create this calculation, follow the steps below and see the attached workbook.

Create the original view

1. Click the link mark right to data field Sub-Category of the secondary data source Sheet1(target)
2. Create a calculation field Diff
SUM([Sales])-SUM([Sheet1 (target)].[Target])
3. Create a calculation field Fail or Succeed to check whether the region reached the sales target of a certain Sub-category or not
if [Diff]>0 then "Succeed"
else "Fail"
END

4. Add Fail or Succeed to Columns, Region to Rows, Sub-Category to Detail
6. Right click Sub-Category, select Measure > Count(Distinct)

Create the solution view

1. Create different calculation fields to calculate COUNTD([Sub-Category]) for Succeed and Fail

Succeed:

if [Diff]>0 then
COUNTD([Sub-Category])
END
Fail:
if [Diff]<=0 then
COUNTD([Sub-Category])
END
2. Create calculation field Solution_Fail or Succeed to calculate the sum of Fail or Succeed of each region
if [Diff]>0 then
window_sum([Succeed])
else
window_sum([Fail])
END

3. Set the Table Calculation of Solution_Fail or Succeed, compute using Sub-Category 4. Create the Filter calculation field to keep only one value show in the view

if [Diff]>0 then
window_sum([Succeed])=running_sum([Succeed])
else
window_sum([Fail])=running_sum([Fail])
END
5. Set the Table Calculation of Filter, compute using Sub-Category 6. Change the selected value of Filter to True.