cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

## if value is negative set 0 screws up the total value

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

1 ACCEPTED SOLUTION
Memorable Member

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

4 REPLIES 4
Memorable Member

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

Community Support

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.

Regular Visitor

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?

Super User

@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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors