Forum Discussion

mayanknamasys's avatar
mayanknamasys
Regular Visitor
1 year ago
Solved

Conditional Running total with set offs

Hi Community,   For last 7 days, I am trying to create a DAX Measure/Calculated Column for calculating a running total with conditional set offs. In short, the logic requires that if the manager ha...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Thanks for the helpful replies from lbendlin.

     

    Hi mayanknamasys ,

     

     

    If you want to sum for different manager names, you can first group by manager names, then add an index column within each group, and apply the List.Accumulate function to calculate the desired output. This ensures that the calculation for each Manager Name is done independently.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dc+/CoMwEMfxV5HMEe40Rh1dHASfIDhcaUEQQhE6+Pat5M+hTbYbPvy4rzFipoPsJqTAciJbVs3vLBAkABTn2btrkRc6vh5MG08Rk3amPViMs1gl7fAOto2zbWb18FJL7aVmOdjPTs+VbmFdqutiuayPtnO//Nl0GWSWuQ1r1nV2O/ShYq28Xr4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Manager Name" = _t, #"Month Name" = _t, #" Monthly Base " = _t, #"Comp@30%" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month Name", type date}, {" Monthly Base ", Currency.Type}, {"Comp@30%", Currency.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Manager Name"}, {{"AllData", each _, type table [#"Manager Name"=nullable text, #"Month Name"=nullable date, #" Monthly Base "=nullable number, #"Comp@30%"=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Desired Output", each 
        let
            GroupedTable = [AllData],
            IndexedTable = Table.AddIndexColumn(GroupedTable, "Index", 0, 1, Int64.Type),
            CustomColumn = Table.AddColumn(IndexedTable, "Desired Output", each List.Accumulate({0..[Index]},0,(s,c)=>List.Min({0,s+IndexedTable{c}[#"Comp@30%"]-IndexedTable{c}[#" Monthly Base "]})))
        in
            CustomColumn),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Desired Output", {"Month Name", " Monthly Base ", "Comp@30%", "Index", "Desired Output"})
in
    #"Expanded Custom"

    Result:

     

    Best Regards,
    Zhu

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

mayanknamasys's avatar
mayanknamasys
Regular Visitor
1 year ago

Hi lbendlin , I will try this List.Accumulate() today but I have not worked with it earlier. If you have worked with it, can you give me some insights on how it might work?

lbendlin's avatar
lbendlin
Super User
1 year ago

 

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dc+/CoMwEMfxV5HMEe40Rh1dHASfIDhcaUEQQhE6+Pat5M+hTbYbPvy4rzFipoPsJqTAciJbVs3vLBAkABTn2btrkRc6vh5MG08Rk3amPViMs1gl7fAOto2zbWb18FJL7aVmOdjPTs+VbmFdqutiuayPtnO//Nl0GWSWuQ1r1nV2O/ShYq28Xr4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Manager Name" = _t, #"Month Name" = _t, #" Monthly Base " = _t, #"Comp@30%" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month Name", type date}, {" Monthly Base ", Currency.Type}, {"Comp@30%", Currency.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Desired Output", each List.Accumulate({0..[Index]},0,(s,c)=>List.Min({0,s+#"Added Index"{c}[#"Comp@30%"]-#"Added Index"{c}[#" Monthly Base "]})))
in
    #"Added Custom"

Now you need to break this up by Manager name.