topic Re: if value is negative set 0 screws up the total value in DAX Commands and Tips

if value is negative set 0 screws up the total value

groetschel — Thu, 06 Oct 2022 17:57:25 GMT

Hi community

I found a strange error in how a total is calculated.

Days to book (3rd column) is a measure: if([To Deliver]-[Planned Days]<0,0,[To Deliver]-[Planned Days])

The values in the table body are all calculated correctly, BUT the total should sum the single values from the table body and not calculate the total of column 1 minus the total of column 2.

How do I do that?

Best regards,

Gunnar

Re: if value is negative set 0 screws up the total value

amitchandak — Fri, 07 Oct 2022 03:44:51 GMT

groetschel , Force to add up from row

Sumx(Values( Table[Column]) , if([To Deliver]-[Planned Days]<0,0,[To Deliver]-[Planned Days]) )

here column is not summarized column in visual.If there are more than one use summarize

Re: if value is negative set 0 screws up the total value

Anonymous — Fri, 07 Oct 2022 05:34:51 GMT

Hi groetschel ,

Here I suggest you to create a new measure based on [Days to book] instead of using sumx function in [Days to book] measure directly.

Measure with correct total = SUMX ( 'tablename', [Days to book] )

I think the columns used in measure [To Deliver] or [Planned days] should come from same table. 'tablename' is that table.

Best Regards,
Rico Zhou

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

Re: if value is negative set 0 screws up the total value

groetschel — Fri, 07 Oct 2022 14:43:15 GMT

I added the new measure

Order Book correct total = sumx('Order Book',[Order Book open days to book])

But the calculation is completely off.

Do you know what it calculates now?

Thanks for your help.

Re: if value is negative set 0 screws up the total value

AilleryO — Fri, 07 Oct 2022 15:47:13 GMT

Hi,

Just keep in mind that the total of your table is not the sum of above lines, but is the same calculation as the one on the lines without the "filter" from your lines.

Sinxe we cannot see it on your screen copy, you have a column on the left part of your table witha book ID or some equivalent.

Thanks to it the detailed lines of your visual are doing the calculation on each lines, whereas the total is calculated with "all" lines from your table. So your test (negative or not) is not relevant on the total which explains the strange value.

As amitchandak propose you, by using a SUMX you force PBI to keep the calculation at the line level.

Hope it makes things more clear