I'm stuck on an issue in PowerQuery involving rounding monthly values but trying to keep the difference in the yearly totals less than 1.

I have data in this format:

As above the yearly total would be 7.9 but with rounding it's 9.

What I want to do is take the total difference each year and use it to adjust the rounded values up or down so that the yearly difference rounds to 0. I also want to ensure the the final rounded values don't drop below 0.

Using the example above the output data would look like this:

Now the difference between the yearly total and the final value total is only 0.1.

I figure I want to write a function that works out the total difference in the year and uses that as a counter to iterate against as it adjusts each row, but I'm not sure how to do that.

Also I realise that maybe a better approach would avoid this issue altogether! Thanks!

CodePudding user response：

"I want to do something with a number but I don't want the number to be negative."

There's a general approach for this kind of question:

=MAX(do_something(number), 0)

Like this, when the result of your operation yields a negative number, it will automatically be replaced by zero.

CodePudding user response：

I don't know how you are generating your Value Rounded column, but the algorithm you are using seems to be round 1/2 away from zero.

The M Number.Round function will round 1/2 to even which reduces the bias inherent in an algorithm that always rounds in the same direction.

Using that, the difference between the sums will only be 0.1.

If that is not good enough, there are other rounding algorithms that you could use. The choice of a rounding algorithm will also depend on the distribution of values. And that is something that cannot be determined by your example. It is certainly possible to construct a set of values for which the differences are large (eg: 0.5 for all values using a round 1/2 to even algorithm), but that would probably be uncommon in real life.

For example:

Original Table

let
    Source = Excel.CurrentWorkbook(){[Name="Table9"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Month", Int64.Type}, {"Value", type number}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Rounded Values", each Number.Round([Value])),

//add totals row
    totals = Table.Combine({#"Added Custom",
     Table.FromRecords({
         [  Year="Totals", 
            Month=null, 
            Value = List.Sum(#"Added Custom"[Value]), 
            Rounded Values = List.Sum(#"Added Custom"[Rounded Values])]})})
     
in
    totals

Result

Edit: Here is code that does what you describe as well as round-to-even. For certain values, your adjustment method seems to work better. I have included both the Round to even and Adjust rounding columns in the results, and also added a Totals row, to make it easier for you to compare the results

Source

M Code

let
    Source = Excel.CurrentWorkbook(){[Name="Table9"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Month", Int64.Type}, {"Value", type number}}),

//round 1/2 to even
    roundToEven = Table.AddColumn(#"Changed Type", "Rounded Values", each  Number.Round([Value])),

//Per your algorithm
    roundAdjust = let 
        nums = roundToEven[Rounded Values],
        adjustmentTotal = Number.Round(List.Sum(#"Changed Type"[Value]) - List.Sum(nums)),
        ns = Number.Sign(adjustmentTotal),
        adjust = List.Generate(
            ()=>[n=List.Max({0,nums{0} Number.Sign(adjustmentTotal)}), adj=adjustmentTotal-Number.Sign(adjustmentTotal), idx=0],
            each [idx] < List.Count(nums),
            each [n=if [adj]=0 then nums{[idx] 1} else nums{[idx] 1} Number.Sign(adjustmentTotal), 
                adj=if [adj]=0 then 0 else [adj]-Number.Sign(adjustmentTotal), idx=[idx] 1],
            each [n])
        in adjust,
//add as a column
    addCol = Table.FromColumns(
        Table.ToColumns(roundToEven) & {roundAdjust},
        type table[Year=Int64.Type, Month=Int64.Type, Value=number, Rounded Values=Int64.Type, Round Adjusted=Int64.Type]
    ),

//add totals row
    totals = Table.Combine({addCol,
     Table.FromRecords({
         [  Year=null, 
            Month="TOTAL:", 
            Value = List.Sum(addCol[Value]), 
            Rounded Values = List.Sum(addCol[Rounded Values]),
            Round Adjusted = List.Sum(addCol[Round Adjusted])
            ]})})
in
    totals

Results