Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Group with Subgroup Total Not Calculating Correctly in Matrix

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. 

Power BI Screenshot.JPG

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!
8 REPLIES 8
Icey
Community Support
Community Support

Hi @Anonymous ,

Is this problem solved?

Best Regards
Icey

Anonymous
Not applicable

Hello @Icey ,

 

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

File 2.JPG

 

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

Icey
Community Support
Community Support

Hi @Anonymous ,

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

 

Best Regards,

Icey

 

Icey
Community Support
Community Support

Hi @Anonymous ,

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

 

Best Regards,

Icey

Anonymous
Not applicable

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

 

data sample.JPG

 

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
Community Support
Community Support

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
Community Support
Community Support

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
Not applicable

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?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.