• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Perform Calculation in three ways

John Jairo V

Well-Known Member
Hi to all!

I have some values in column, and I want to calculate the cumulative values with upper limit: 500.
I'd like to perform this task (using Power BI) in:

1. M (PowerQuery)
2. Calculated Column (DAX) and
3. Measure (DAX)

Just for verify how to calculate in this three states.

Check the file. Blessings!
 

Attachments

Chihiro

Excel Ninja
This type of calculation is difficult to do in PowerBI space, as M nor DAX allows for self referencing calculation (i.e. Calculated column referencing calculation result of it's own previous rows).

Having said that, this is interesting challenge and best done in M using list function(s), specifically List.Accumulate.
I'm headed into meeting now, but will see if I have time later today to tackle this.
 

Chihiro

Excel Ninja
Ok, had bit of time this morning. Below is the custom function. Instead of using List.Accumulate, I ended up going with List.Generate.
Code:
(myVals as list) =>
let
    vCount = List.Count(myVals) + 1,
    calVals = List.Generate(()=>[x=0, y={}, z=0, Original=myVals{0}], each [x] < vCount, each [x = [x]+1,
                            y = [y] & {if x = 1 then myVals{0} else if (List.Sum(List.FirstN(myVals,x)) - List.Sum(List.Transform([y], each Number.From(_)))) > 500 then 500 else
                            (List.Sum(List.FirstN(myVals,x)) - List.Sum([y]))}, z = y{x-1}, Original=myVals{x-1}]),
    res = List.LastN(calVals, vCount -1)
in
    res
You invoke it using column from table (i.e. your values column).
65578

This will generate list of records [x, {y}, z, Original].
y is list itself and used to hold calculated values at each stage as list for further processing. (Edited for clarity)
Original is just there to retain original value, as you can't join back invoked function result back onto source (you could, if you create copy of original and base your column on that one).

Convert the list into table and expand x, z & Original. Adjust column name(s) and position as needed.
65579
 
Last edited:

Chihiro

Excel Ninja
Oh, just realized I forgot to remove List.Transform(). It was only used for trouble shooting some issues during building logic.
This...
Code:
List.Sum(List.Transform([y], each Number.From(_)))
Can be simplified to...
Code:
List.Sum([y])
 

John Jairo V

Well-Known Member
Nice! I have to break this into small parts for understanding, and I have to study functions in M code. Thx for sharing!

If you have time to check the possible solutions in DAX (calculated columns and measures), would be nice too. Blessings!
 
Top