cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
leudyston Regular Visitor
Regular Visitor

Remove Negative Values ​​with Calculate

Hello friends, I need to create a measure that can only bring the positive values ​​of an expression and that the total of this measure is also calculated only by adding the positive values, I have tried in every way to use the CALCULATE and the possible filters but I did not succeed .
In the attached image I did a demonstration of how the correct form of the values ​​would appear, I did this with the IF, which unfortunately does not bring the correct TOTAL value.

I am also attaching the link so they can download the .pbix file.

I await solutions, thank you !!

 

file .pbix

image.jpg

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User IV
Super User IV

Re: Remove Negative Values ​​with Calculate

Hi,

 

Try this revised measure

 

=if(HASONEVALUE(ditens[Tamanho]),IF([Calc Sugestão de Compras]>=0,[Calc Sugestão de Compras],0),SUMX(SUMMARIZE(VALUES(ditens[Tamanho]),[Tamanho],"ABCD",IF([Calc Sugestão de Compras]>=0,[Calc Sugestão de Compras],0)),[ABCD]))

 

Hope this helps.

 

Untitled.png


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

View solution in original post

9 REPLIES 9
Super User IV
Super User IV

Re: Remove Negative Values ​​with Calculate

Hi,

 

I'd like to help but the file size is very large.  Trim the file to a couple of MB's and then share the download link.


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

Re: Remove Negative Values ​​with Calculate

Hello @Ashish_Mathur, thank you for helping me with this question.

 

I decrease the file size to 5Mb, the Stock Balance values ​​will not be the actual values, but what matters is how much I need to calculate correctly.

 

File .pbix

Super User IV
Super User IV

Re: Remove Negative Values ​​with Calculate

Hi,

 

Try this revised measure

 

=if(HASONEVALUE(ditens[Tamanho]),IF([Calc Sugestão de Compras]>=0,[Calc Sugestão de Compras],0),SUMX(SUMMARIZE(VALUES(ditens[Tamanho]),[Tamanho],"ABCD",IF([Calc Sugestão de Compras]>=0,[Calc Sugestão de Compras],0)),[ABCD]))

 

Hope this helps.

 

Untitled.png


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

View solution in original post

leudyston Regular Visitor
Regular Visitor

Re: Remove Negative Values ​​with Calculate

Hi @Ashish_Mathur,

 

In the context of the First Table is correct, but when I filter more than one store, the second table that shows the values ​​per store are not correct, I tried to change the measure but it is well advanced and unfortunately I could not.

Note: In the pbix file I had to move almost all the stores, I left only the movement of the Manhattan and Aldeota stores, I think it is important to inform this so that you can understand, since when filtering any other stores, the values ​​will be zeroed.

 

Thank you so much for helping me.

Merry Christmas

 

IMAGE2.jpg

 

 

 

Super User IV
Super User IV

Re: Remove Negative Values ​​with Calculate

Hi,

 

There is some problem with your measure "Calc Sugestão de Compras".  When i select All Stores and also drag this measure to the last table, i see all negative values for Store Aldeota.

 

Please check that measure.


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

Re: Remove Negative Values ​​with Calculate

Okay, I discovered the problem, as far as CALC SUGESTÃO COMPRAS, the ESTOQUE ATUAL measure that is part of the measures that generate the CALC SUGESTÃO COMPRAS is with filters wrong, so the solution you gave me is very correct.

Thank you so much!!

Merry Christmas!!

Super User IV
Super User IV

Re: Remove Negative Values ​​with Calculate

You are welcome.


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

Re: Remove Negative Values ​​with Calculate

Hello @Ashish_Mathur

 

After fixing the error in the ESTOQUE ATUAL, continue with other validations and encountered this problem. When the context brings the result by Store, it is adding the negative value with the positive, giving divergence in the final value of the measure. Affecting the value per line of the measure SUGESTÃO DE COMPRAS.

 

Can you help me find this problem as you created it?

 

As this error only appeared when I was validating another store, I had to add its moves in the .pbix file, then follow the updated file link with store 33 MESSEJANA.

FILE .PBIX

image.jpg

 

 

 

 

Highlighted
Super User IV
Super User IV

Re: Remove Negative Values ​​with Calculate

Hi,

 

I cannot appreciate the problem - it is simply because of the language.  I may not be able to help.


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

Helpful resources

Announcements
Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors