I need help correcting a measurement and summarizing the result

[Ferhyox]

Hello everyone.

I'm a relatively new user, and I'd like your help with this problem. 

I have a table visual, where the first column is the names of the Customers in my base, and 4 measures, I'll call them A, B, C and D.

1. A is the sum of a customer debt value for the last three months.
2. B is the result of A divided by the number of days in the last 3 months, multiplied by a base factor depending on the client. For most clients, this factor is 75, but there are some exceptions where it is 60.
3. C is a measure that gives the result of the entire value of the client's credit balance.
4. D is the result of C minus B, but if the result is negative, then I need to return zero instead of the negative value.

So far, I've come up with the following DAX measure for 😧

D = 
    VAR _B = [B]
    VAR _C = [C]
    VAR _D =  
            SWITCH(
                TRUE(),
                OR(_C < 0, _B < 0), 0,
                _C > _D, 0,
                _C - _B
            )
RETURN
    _D

The calculation works, but I need to add up the values that are not zero in the total row, and I believe that due to the logic of the calculation this doesn't happen. 

Could someone help me with this problem?

Thanks in advance.

[rajendraongole1 · Accepted solution]

Hi @Ferhyox - Create a measure to handle the total row and ensure it only sums non-zero values as below:

D =
SUMX(
VALUES('CustomerTable'[CustomerName]),
VAR _B = [B]
VAR _C = [C]
VAR _D = _C - _B
RETURN IF(_D < 0, 0, _D)
)

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[Ferhyox]

I only made a small adaptation by changing the IF for a SWITCH to add some validations, but your suggestion worked perfectly.

Thank you very much @rajendraongole1 ! 😁