Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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.
Hello,
you simply need to add MAX to exclude negative values
Net Increas:=SUMX(Table;MAX([INCREASE]-[DECREASE];0))
Best regards
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
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
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.
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.
User | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
73 | |
65 |