if value is negative set 0 screws up the total value

[groetschel]

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

[AilleryO · Accepted solution]

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

[v-rzhou-msft]

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.

[groetschel]

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.

[amitchandak]

@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