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
krootz
Frequent Visitor

Total on Matrix is not what I want

Hello, 

I have a list of products with an increase, decrease and a measure to calculate the net increase based on an IF statement.

If the increase > decrease, then the net increase = increase - decrease, else 0. 

The net increase shows correctly per product in the matrix.

However, the total of the net increase is not the sum of the individual net increases. The IF logic is also applied at the total increase and total decrease which is wrong. The net increase is not the total increase - total decrease, but the sum of the individual net increases. See table below (26.78 is wrong, the total net increase is 68.44).

 

What's the work around to show the correct value at the total? 

 

PRODUCTINCREASEDECREASENET INCREASE 
74130802312.962.370.59 
743005018103.910 
743005038116.62016.62 
743005069138.2038.2 
7430106739035.840 
74301707514.364.360 
743017084117.5817.60 
743017139145.645.60 
74360305717.26.310.89 
743603058101.890 
74360310318.628.620 
748644424113.651.5112.14 
TOTAL154.79128.0126.78wrong total
   68.44correct total

 

Thanks,

Jay

1 ACCEPTED SOLUTION

Hi,

 

Try this measure

 

=IF(HASONEVALUE(CHANGE_LOG[MESC]),IF(SUM(CHANGE_LOG[INCREASED_QTY])>SUM(CHANGE_LOG[DECREASED_QTY]),SUM(CHANGE_LOG[INCREASED_QTY])-SUM(CHANGE_LOG[DECREASED_QTY]),0),SUMX(SUMMARIZE(VALUES(CHANGE_LOG[MESC]),CHANGE_LOG[MESC],"ABCD",IF(SUM(CHANGE_LOG[INCREASED_QTY])>SUM(CHANGE_LOG[DECREASED_QTY]),SUM(CHANGE_LOG[INCREASED_QTY])-SUM(CHANGE_LOG[DECREASED_QTY]),0)),[ABCD]))

 

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

6 REPLIES 6
Floriankx
Solution Sage
Solution Sage

Hello,

 

you simply need to add MAX to exclude negative values

Net Increas:=SUMX(Table;MAX([INCREASE]-[DECREASE];0))

 

Best regards

Greg_Deckler
Super User
Super User

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

 

That being said, you may see success with using the ALLSELECTED function, but it depends.

 

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Thanks for the reply Greg. But to be more specic, here's the issue:

 

I have the matrix below, they are not individual rows but is an aggregate at the MESC level, each MESC can have multiple records for increases/decreases.  For each MESC, I need to calculate the net increase only when at MESC level, total increase - total decrease > 0.

 

The measure that I used is based on a solution I saw here (http://community.powerbi.com/t5/Desktop/Matrix-Table-Total-Row-not-calculating-totals/m-p/182867#M80...). 

 

NET INCREASE = IF(COUNTROWS(VALUES(CHANGE_LOG[MESC]))=1,
IF(SUM(CHANGE_LOG[INCREASED_QTY])>SUM(CHANGE_LOG[DECREASED_QTY]),SUM(CHANGE_LOG[INCREASED_QTY])-SUM(CHANGE_LOG[DECREASED_QTY]),0),
SUMX(VALUES(CHANGE_LOG[MESC]),IF(SUM(CHANGE_LOG[INCREASED_QTY])>SUM(CHANGE_LOG[DECREASED_QTY]),SUM(CHANGE_LOG[INCREASED_QTY])-SUM(CHANGE_LOG[DECREASED_QTY]),0)))

 

The resulting total, should be the sum of what you see visually in the NET INCREASE column which is supposed to be 68.44, but it's being inflated to 321.36 

net.png

Can you tell me what I'm doing wrong here please?

I appreaciate your help!

 

Thanks,

Jay

Rio
Frequent Visitor

Hi, how do you change the column header into NET INCREASE? i have the same table and calculation but my column header is Total and i can't change it. Smiley Indifferent

krootz
Frequent Visitor

That is a measure I created with the name NET INCREASE.

Hi,

 

Try this measure

 

=IF(HASONEVALUE(CHANGE_LOG[MESC]),IF(SUM(CHANGE_LOG[INCREASED_QTY])>SUM(CHANGE_LOG[DECREASED_QTY]),SUM(CHANGE_LOG[INCREASED_QTY])-SUM(CHANGE_LOG[DECREASED_QTY]),0),SUMX(SUMMARIZE(VALUES(CHANGE_LOG[MESC]),CHANGE_LOG[MESC],"ABCD",IF(SUM(CHANGE_LOG[INCREASED_QTY])>SUM(CHANGE_LOG[DECREASED_QTY]),SUM(CHANGE_LOG[INCREASED_QTY])-SUM(CHANGE_LOG[DECREASED_QTY]),0)),[ABCD]))

 

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.