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

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

Create the original view

SUM([Sales])-SUM([Sheet1 (target)].[Target])

if [Diff]>0 then "Succeed"
else "Fail"
END

4. Add Fail or Succeed to Columns, Region to Rows, Sub-Category to Detail
5. Add Sub-Category to Columns
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

if [Diff]<=0 then 
COUNTD([Sub-Category])
END

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

Sub-Category