If statement not totalling in matrix

[mwen90]

Hi, 

I have a scenario where I am trying to calculate Column A - Column B. However, some of my Column A items are 0, so the solution is negative Column B value. 

Where this happens, I want to remove it. But all the if statements Im trying aren;t working.

See the table above. In the first row, Column 3 is calculating correctly. But when I do, if column 3 < 0, Blank, sum(Column 3))

But it comes up as 240, not 199.85?

Thanks for the help!

[V-pazhen-msft]

@mwen90 

Since you have many columns in the table, the columns in the table will sum up automatically based on the fields you included in the table. 199.85 is also a sum value of the rows with "Core", "LG", "IPU". For such many columns, it is suggested to create a measure instead of a column. Because measure is perfectly calculated based on the included filters and fields on the report.

Measure 2 = IF(SUM(Reinforcement[Column 3])<0,BLANK(),SUM(Reinforcement[Column 3]))

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

[mwen90]

Hey @V-pazhen-msft 

Hope you're well, 

Just following up if you know how to make the formula you suggested total in the matrix? As shown below, i can't get the totals to show for my if statement?

Thanks again for the help!

[mwen90]

@V-pazhen-msft  it worked, but there's no total at the bottom of the tbale? Do you know how to get it to total? Thanks so much for the help! 

[amitchandak]

@mwen90 , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Make sure this is the new column

Column 2=  if([Column3]<0, blank(), [Column3])

[mwen90]

@amitchandak  further, if I do the formula you suggested, the coloum 3 value isn't the same?

It's 199.85 in Column 3 , Column 4 is 240?

Cheers,

[mwen90]

Thanks @amitchandak  see link to access sample data https://drive.google.com/drive/folders/1o-w5BZBVZCHkfNboDbWg5soJBsnXENTO?usp=sharing 

Please note, I just want to recreate Column 3, but only display the positive value of 199.85. I am hoping this will also display as the total.

Thank you so much for the help!

[Sirhawk3017]

Did you find an answer to this? Having the same problem.

[mwen90]

Yeah, I used Sumx(Summarize(......). I then input my table and the columns which were in the matrix. 

You need to play around with the order of the summarize.