Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
groetschel
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

 

Screenshot 2022-10-06 115043.png

1 ACCEPTED SOLUTION
AilleryO
Memorable Member
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

View solution in original post

4 REPLIES 4
AilleryO
Memorable Member
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

v-rzhou-msft
Community Support
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.

 

I added the new measure

Order Book correct total = sumx('Order Book',[Order Book open days to book])
But the calculation is completely off.
Screenshot 2022-10-07 084114.png

Do you know what it calculates now?

Thanks for your help.

amitchandak
Super User
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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors