Group with Subgroup Total Not Calculating Correctly in Matrix

[Anonymous]

Good morning, 

I am trying to calculate the Net value of two seperate columns. I only want to display values that are below zero.

Below is the data I am using and while it calculates the individual information correctly, it does not calculate the group total correctly. 

Here is my formula typed out:

Net 60 = IF((SUM('Excess Credits'[Positive])+SUM('Excess Credits'[Credit 60]))>0,0,(SUM('Excess Credits'[Positive])+SUM('Excess Credits'[Credit 60])))

Appreciate any help!

[Icey]

Hi @Anonymous ,

Is this problem solved?

Best Regards
Icey

[Anonymous]

Hello @Icey ,

It does not work when I enter it as a measure. Output below:

The Total is still 0. It should be -33,006.2

[Icey]

Hi @Anonymous ,

If you don't mind, please share me your PBIX file without real data and sensitive information.

Best Regards,

Icey

[Icey]

Hi @Anonymous ,

Please share me some data sample with the same structure of your real data.

Best Regards,

Icey

[Anonymous]

@Icey  See below for the format of my data. Is this what you are looking for?

The final 3 columns are added conditional columns.

Positive: If Invoice Type = Standard Then Outstanding Amount

Credit 60: If Invoice Type = Credit Memo Then Over 60 Days

Credit 30: If Invoice Type = Credit Memo Then Over 30 Days

[Icey]

Hi @Anonymous ,

The above DAX expression I posted is a Measure, not a Calculated Column. Please try it again.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Icey]

Hi @Anonymous ,

Try this:

Net 60 =
IF (
    HASONEFILTER ( 'Excess Credits'[Buyer] ),
    IF (
        ( SUM ( 'Excess Credits'[Positive] ) + SUM ( 'Excess Credits'[Credit 60] ) ) > 0,
        0,
        ( SUM ( 'Excess Credits'[Positive] ) + SUM ( 'Excess Credits'[Credit 60] ) )
    ),
    SUM ( 'Excess Credits'[Positive] ) + SUM ( 'Excess Credits'[Credit 60] )
)

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you @Icey ,

I input that formula however, it is still showing zero in the Total. Do I need to use a SUMX or SUMMARIZE in my formula?