How do you show negative number in matrix without subtracting from the total

[Anonymous]

@ links to members, content

Hello, I am not sure how to write DAX for my calculation needed in a matrix.  I am showing figures within a group some of the figures are negative numbers.  It appears the matrix will add all the numbers and then subtract the negative value from the total.  This is not what I want.  I want to show the negative value but I do not want the negative value to be subtracted from the positive values. 

Here is my sample 

Fund TypeSegment Donor FlowYearSum of DonorsColumn1     The total comes out as 20465 which is not correct.  The matrix is totaling all positive numbers and then subtracting the negative number in red.  I would also like to highlight the negative numbers in red in the matrix.                     

26582 This is the correct total I want to see in the Matrix

General	Houston	Keep	2021	7877
General	Houston	Less	2021	3927
General	Houston	Lift	2021	6773
General	Houston	Lost	2021	-6117
General	Houston	Win	2021	5662
General	Houston	Win-Back	2021	2343

[Anonymous]

Sample Data

Fund        Segment   Flow         Year      Donor

General    Houston    Keep        2021      7877

General    Houston    Less          2021      3927

General    Houston    Lift            2021      6773

General    Houston    Loss          2021     -6117

General    Houston    Win           2021      5662

General    Houston   Win-back   2021       2343

Total                                                         20465 Wrong Total 

Correct Total should be 26,582

[Ashish_Mathur]

Hi,

Share some data to work with (in a format that can be pasted in an MS Excel file).  At the very least, ensure that the columns have proper headings.

[Anonymous]

Hello, I am not sure how to write DAX for my calculation needed in a matrix.  I am showing figures within a group some of the figures are negative numbers.  It appears the matrix will add all the numbers and then subtract the negative value from the total.  This is not what I want.  I want to show the negative value but I do not want the negative value to be subtracted from the positive values. 

Here is my sample 

Fund TypeSegment Donor FlowYearSum of DonorsColumn1     The total comes out as 20465 which is not correct.  The matrix is totaling all positive numbers and then subtracting the negative number in red.  I would also like to highlight the negative numbers in red in the matrix.     

Sample Data

Fund        Segment   Flow         Year      Donor

General    Houston    Keep        2021      7877

General    Houston    Less          2021      3927

General    Houston    Lift            2021      6773

General    Houston    Loss          2021     -6117

General    Houston    Win           2021      5662

General    Houston   Win-back   2021       2343

Total                                                         20465 Wrong Total 

Correct Total should be 26,582

[Ashish_Mathur · Accepted solution]

Hi,

These measures work

D = sum(Data[Donors])

Measure = if(ISINSCOPE(Data[Year]),[D],SUMX(FILTER(Data,[D]>0),[D]))

Hope this helps.

[Anonymous]

Ashish, 

The measure works, however, it removes by negative number.  I would like for my negative number to show in the matrix. 

[Anonymous]

Ashish, 

Thank you for your assistance.  I got it to work by changing the Year filter to Donor Flow.  

[Ashish_Mathur]

You are welcome.

[Anonymous]

Sample Data

Fund        Segment  Flow          Year    Donors

General	Houston	Keep	2021	7877
General	Houston	Less	2021	3927
General	Houston	Lift	2021	6773
General	Houston	Lost	2021	-6117
General	Houston	Win	2021	5662
General	Houston	Win-Back	2021	2343