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 has a deficit during the month, it will carried forward to next months but if the manager has surplus, the surplus will either be paid during the month or set off against the deficit of previous months.

 

The detailed logic with examples is explained in the column K of attached excel as well as the screenshot.

 

I have been able to create this logic in Excel file (Column H) quite easily because excel provides control over each row but I have had no success yet in DAX (I have attached the Excel file with solution as well as the power bi file with sample data). 



Thank you in Advance.

Excel file link:  data.xlsx
Power BI file link: data.pbix

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.

4 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    1 year ago

    Conditional aggregation is impossible in DAX.  Your only chance of implementing this is in Power Query via List.Accumulate()

    • 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.

         

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    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.