cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mdw012 Frequent Visitor
Frequent Visitor

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

Re: Group with Subgroup Total Not Calculating Correctly in Matrix

Hi @mdw012 ,

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.

 

Highlighted
mdw012 Frequent Visitor
Frequent Visitor

Re: Group with Subgroup Total Not Calculating Correctly in Matrix

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?

Community Support Team
Community Support Team

Re: Group with Subgroup Total Not Calculating Correctly in Matrix

Hi @mdw012 ,

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

 

Best Regards,

Icey

mdw012 Frequent Visitor
Frequent Visitor

Re: Group with Subgroup Total Not Calculating Correctly in Matrix

@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

Community Support Team
Community Support Team

Re: Group with Subgroup Total Not Calculating Correctly in Matrix

Hi @mdw012 ,

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.

Community Support Team
Community Support Team

Re: Group with Subgroup Total Not Calculating Correctly in Matrix

Hi @mdw012 ,

Is this problem solved?

Best Regards
Icey

mdw012 Frequent Visitor
Frequent Visitor

Re: Group with Subgroup Total Not Calculating Correctly in Matrix

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

Community Support Team
Community Support Team

Re: Group with Subgroup Total Not Calculating Correctly in Matrix

Hi @mdw012 ,

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

 

Best Regards,

Icey

 

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)