topic Re: If formula returning unexpected result for negative values (summarizing all unique negative values) in DAX Commands and Tips

If formula returning unexpected result for negative values (summarizing all unique negative values)

hansdunnik — Sun, 25 Sep 2022 19:52:34 GMT

I have the following values (simplistic).

Currency Value

EUR 10

EUR -5

USD -3

JPY 5

JPY -4

I have a column in Power BI where i want to find the total of value per currency, this will show as following

EUR 5

USD -3

JPY 1

What i want now is to add a new column or measure and I would like to apply an IF statement.

If total value per currency is less than zero, then show this total value per currency, otherwise show 0 or blank.

What i want to see is this

EUR 0 or blank

USD -3

JPY 0 or blank

But it does not matter which DAX formula I try, but it is giving the wrong output and mostly it will show me the following:

EUR -5

USD -3

JPY -4

It is showing the negative values per currency, but i would like the DAX to keep on to the cumulated totals and then only apply the IF statement... but that is not working out for me 😞

Can anyone guide me into a similar question and answer or help me with a sample formula ?

Re: If formula returning unexpected result for negative values (summarizing all unique negative values)

v-yueyunzh-msft — Mon, 26 Sep 2022 03:10:07 GMT

Hi, hansdunnik

According to your description, you want to convert the sum of [Value] to the BLANK() which is less than 0. Right?

Here are the steps you can follow:

(1)This is my test data:

(2) If you want to show in a table ,you can click "New Table" and enter:

Table 2 = SUMMARIZE('Table','Table'[Currency] , "value" ,IF( SUM('Table'[Value]) <0 ,SUM('Table'[Value]),BLANK()) )

The result is as follows:

(3)If you want to show in a visual ,you can click "New measure" and enter:

v = IF( SUM('Table'[Value]) <0 , SUM('Table'[Value]),BLANK())

Then we can put the [v] measure and the [Currency] field in the table visual, the result is as follows:

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Re: If formula returning unexpected result for negative values (summarizing all unique negative values)

hansdunnik — Fri, 30 Sep 2022 13:47:23 GMT

I am sorry Aniya for not replying earlier. I only found time today to look into it.

This did help me to get to the next step. I am seeing indeed now the USD -3 that I want to see....

But it is not getting me a Totalized amount in the column. Even when changing BLANK into 0 in the measure formula, it shows me zero. So probably I need another fix in the formula in the last part.

Currently you had:

v = IF( SUM('Table'[Value]) <0 , SUM('Table'[Value]),BLANK())

Probably the last part needs to be updated somehow, to also show -3 in the total line ??

v = IF( SUM('Table'[Value]) <0 , SUM('Table'[Value]),??????())

Re: If formula returning unexpected result for negative values (summarizing all unique negative values)

v-yueyunzh-msft — Mon, 03 Oct 2022 01:25:37 GMT

Hi , hansdunnik

According to your description, you want to display the "Total" in the visual . And in this sample data ,the total is "-3". Right?

Here are the steps you can refer to :

(1)This is my test data: 'Sheet1' Table

(2)We can click "New Table" to create a table :

Table 2 = SUMMARIZE('Sheet1','Sheet1'[Currency] , "value" ,IF( SUM('Sheet1'[Value]) <0 ,SUM('Sheet1'[Value]),BLANK()) )

(3)Then we can put the fields in 'Table2' , then we can meet your need :

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly