Expression to Hide row - affect another expression with Sum(total <Dim> spend)

[#1]

Dear Community,

Kindly help to solve this expression.

Background:

There are 4 tier types, each tier type has different formula to calculate AVB cash. (Formula is written in AVB Cash expression)

Each tier type will have more than one tier level depend on the MinSpend and MaxSpend accordingly.

User will only view one tier level where the Full Year Estimate of the dealID is between MinSpend and MaxSpend.

With the AVB cash expression, I am able to get the correct result as shown below:

But we need to hide all the other rows where Tier Achieved is null, there is one dimension called Hide row, when I select suppress null, it will hide all the unwanted row. Somehow, the problem is after hiding row, the AVB cash value has changed to incorrect, this might be caused by the AVB cash expression with (Sum(Total <Dim> spend)

Do we have any script to hide row without affecting the AVB cash calculation?

Thanks and best regards,

Chanel