Skip to main content

Notifications

Finance | Project Operations, Human Resources, ...
Suggested answer

How do you round up the sum of the values?

(0) ShareShare
ReportReport
Posted on by 737

Hi,

I want to round up to an integer, the sum of the values in a given field. What will be the expression for this?

I've tried with this before:

Sum(Round(Fields!MyField.Value, 1))

Format(Sum(Fields!MyField.Value),0)

Sum(Round(Fields!MyField.Value, 0, System.MidpointRounding.AwayFromZero))

The requirements: for example, the numbers 6.1 or 6.4 or 6.8 - I want always display as 7, but I always get 6 as a result..

Thanks.

  • Suggested answer
    Bharani Preetham Peraka Profile Picture
    Bharani Preetham Pe... 3,587 Super User 2024 Season 1 on at
    RE: How do you round up the sum of the values?

    You can simply try using roundup function in d365fo in dp class. This will round to next number which satisfies your requirement.

    For more information you can follow this link

    d365ffo.com/.../

  • GirishS Profile Picture
    GirishS 27,821 Super User 2024 Season 1 on at
    RE: How do you round up the sum of the values?

    Sorry to say that - I guess only Ceiling and Floor functions are available to round up or round down the values.

    I don't have any idea on this. It will be better if you go with changing the rounding in DP class, instead of trying with report expression.

    I understand you will have calculation on the DP class but from my view rounding the values in DP class is the only option.

    Thanks,

    Girish S.

  • Shooowtek Profile Picture
    Shooowtek 737 on at
    RE: How do you round up the sum of the values?

    Unfortunately, the floor does not work as we would expect. It returns a result of 23...

  • GirishS Profile Picture
    GirishS 27,821 Super User 2024 Season 1 on at
    RE: How do you round up the sum of the values?

    First expression is summing up all the value and do a ceiling.

    For the second expression instead of ceiling use floor function

    But using floor function may work for that case. But for some cases it will fail.

    If that works you need to check for all the scenarios.

    Thanks,

    Girish S.

  • Shooowtek Profile Picture
    Shooowtek 737 on at
    RE: How do you round up the sum of the values?

    6.5 + 14.8 + 3.2 = 24.2 and it probably rounds up to 25

    It should be 26, because it should add up the values already rounded up, i.e. 7 + 15 + 4 = 26.

    What I don't understand is why it returns 25 as the result. In this expression Sum(CEILING(Fields!Qty.Value)) I am adding up the values rounded up, so I don't know why only the final total is rounded up....

    In addition, I don't understand why it returns 29 in this expression....CEILING(Sum(Fields!Qty.Value))

    It follows that for the value 6.5 it takes 8, for 14.8 it takes 16 and for 3.2 it takes 5 (8+16+5 = 29)

    Or is it the case here that on a row it is already rounded up, e.g. from 6.5 to 7, and if I later sum it by category it again the value rounded up from 7 to 8?

  • GirishS Profile Picture
    GirishS 27,821 Super User 2024 Season 1 on at
    RE: How do you round up the sum of the values?

    Ok, how you are assuming value must be 26 instead of 25?

    what are all the values that sums up to 25?

    Thanks,

    Girish S.

  • Shooowtek Profile Picture
    Shooowtek 737 on at
    RE: How do you round up the sum of the values?

    I just can't do it in the DP class because I have other calculations and operations there that are mutually exclusive with rounding. I have to round everything directly on the report.

  • GirishS Profile Picture
    GirishS 27,821 Super User 2024 Season 1 on at
    RE: How do you round up the sum of the values?

    I guess its better to do it on the dp class itself. You can use roundup function to achieve the same thing.

    In that case you know need to worry about the rounding when group by, since the temp table itself has rounded value.

    Thanks,

    Girish S.

  • Shooowtek Profile Picture
    Shooowtek 737 on at
    RE: How do you round up the sum of the values?

    Thank you GirishS.

    Overall your suggestion is working. However, when I want to sum these values by category, unfortunately I do not get the correct total. I tried different variants of CEILING can not get the correct result.

    =Sum(CEILING(Fields!Qty.Value)) => result  25

    =CEILING(Sum(Fields!Qty.Value)) => result  29

    Could you suggest how I should proceed?

    I attach a helpful sketch.

    258151.png

  • Suggested answer
    GirishS Profile Picture
    GirishS 27,821 Super User 2024 Season 1 on at
    RE: How do you round up the sum of the values?

    Hi Shooowtek ,

    Try using CEILING function in expression.

    CEILING(Sum(Fields!MyField.Value))

    If it doesn't work, you can do it from DP class - Using roundUp.

    Thanks,

    Girish S.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Vahid Ghafarpour – Community Spotlight

We are excited to recognize Vahid Ghafarpour as our February 2025 Community…

Congratulations to the January Top 10 leaders!

Check out the January community rock stars...

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 292,074 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 230,900 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Product updates

Dynamics 365 release plans