Total on Matrix is not what I want

[krootz]

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? 

PRODUCT	INCREASE	DECREASE	NET INCREASE
7413080231	2.96	2.37	0.59
7430050181	0	3.91	0
7430050381	16.62	0	16.62
7430050691	38.2	0	38.2
7430106739	0	35.84	0
7430170751	4.36	4.36	0
7430170841	17.58	17.6	0
7430171391	45.6	45.6	0
7436030571	7.2	6.31	0.89
7436030581	0	1.89	0
7436031031	8.62	8.62	0
7486444241	13.65	1.51	12.14
TOTAL	154.79	128.01	26.78	wrong total
			68.44	correct total

Thanks,

Jay

[Floriankx]

Hello,

you simply need to add MAX to exclude negative values

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

Best regards

[Greg_Deckler]

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

[krootz]

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 

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

I appreaciate your help!

Thanks,

Jay

[Rio]

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.

[krootz]

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

[Ashish_Mathur · 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.