cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
New Member

Subtract running total based on multiple conditions

Hi, 

 

I've been trying to do calculate a running total that subtracts based on multiple conditions in power query, im thinking DAX might be more suitable. 

 

The product might change but it needs the last column needs to subtract based on the conditions that it needs to be the same product/store/warehouse. I hope that makes sense.

Also the total available is just in this table to make it easier to understand, i know its not the best way to represent the data.

 

Some sample data for example: 

StoreProductwarehouse numberQTYTOTAL available at relevant warehouseSubtract running total if available
123APPLE15105
123APPLE14 1
123APPLE11 0
123APPLE13 0
789APPLE26159
789APPLE26 3
789APPLE26 0
789APPLE26 0
789APPLE26 0
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Solution Sage
Solution Sage

Re: Subtract running total based on multiple conditions

assuming there are no duplicate rows, we can make use of the Table.PositionOf function and avoid the steps to introduce the auxiliary index column and then to eliminate it.

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRcgwI8HEF0oZAbAqiDcCMWB1sCkyAGMzELm0IkTbAIW2MLG1uYYkkbQTEZiBlICdY4lEANgavND7jiZKOBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Store = _t, Product = _t, war = _t, QTY = _t, TOTAL = _t, CheckSub = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"TOTAL", type number}, {"QTY", type number}}),
    grp= Table.Group(#"Changed Type",  "TOTAL", {"mmm",  (r)=>   Table.AddColumn(r,"sub", each List.Max({r[TOTAL]{0}-List.Accumulate({0..Table.PositionOf(r,_)},0,(s,c)=>s+r[QTY]{c}),0}))},GroupKind.Local, (x,y)=>Number.From( x=y or  y<>null)),
    #"Expanded mmm" = Table.ExpandTableColumn(grp, "mmm", {"Store", "Product", "war", "QTY", "CheckSub", "sub"}, {"Store", "Product", "war", "QTY", "CheckSub", "sub"})
in
    #"Expanded mmm"

 

 

View solution in original post

2 REPLIES 2
Highlighted
Solution Sage
Solution Sage

Re: Subtract running total based on multiple conditions

of course there are simpler and perhaps direct solutions, but just to use some particular functions that MS developers offer us, ...

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRcgwI8HEF0oZAbAqiDcCMWB1sCkyAGMzELm0IkTbAIW2MLG1uYYkkbQTEZiBlICdY4lEANgavND7jiZKOBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Store = _t, Product = _t, war = _t, QTY = _t, TOTAL = _t, CheckSub = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"TOTAL", type number}, {"QTY", type number}}),
    grp= Table.Group(#"Changed Type",  "TOTAL", {"mmm",  (r)=>   Table.AddColumn(Table.AddIndexColumn(r,"idx",0,1),"sub", each List.Max({r[TOTAL]{0}-List.Accumulate({0.._[idx]},0,(s,c)=>s+r[QTY]{c}),0}))
                                                                
                                                },GroupKind.Local, (x,y)=>Number.From( x=y or  y<>null)),
    #"Expanded mmm" = Table.ExpandTableColumn(grp, "mmm", {"Store", "Product", "war", "QTY", "CheckSub", "idx", "sub"}, {"Store", "Product", "war", "QTY", "CheckSub", "idx", "sub"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded mmm",{"idx"})
in
    #"Removed Columns"

 

 

this solution is based on the fact that the empty cells of the TOTAL column are set to null.
If this value is different, for example empty, you have to change the corresponding value in the expression in the table.group

Highlighted
Solution Sage
Solution Sage

Re: Subtract running total based on multiple conditions

assuming there are no duplicate rows, we can make use of the Table.PositionOf function and avoid the steps to introduce the auxiliary index column and then to eliminate it.

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRcgwI8HEF0oZAbAqiDcCMWB1sCkyAGMzELm0IkTbAIW2MLG1uYYkkbQTEZiBlICdY4lEANgavND7jiZKOBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Store = _t, Product = _t, war = _t, QTY = _t, TOTAL = _t, CheckSub = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"TOTAL", type number}, {"QTY", type number}}),
    grp= Table.Group(#"Changed Type",  "TOTAL", {"mmm",  (r)=>   Table.AddColumn(r,"sub", each List.Max({r[TOTAL]{0}-List.Accumulate({0..Table.PositionOf(r,_)},0,(s,c)=>s+r[QTY]{c}),0}))},GroupKind.Local, (x,y)=>Number.From( x=y or  y<>null)),
    #"Expanded mmm" = Table.ExpandTableColumn(grp, "mmm", {"Store", "Product", "war", "QTY", "CheckSub", "sub"}, {"Store", "Product", "war", "QTY", "CheckSub", "sub"})
in
    #"Expanded mmm"

 

 

View solution in original post

Helpful resources

Announcements
Meet the 2020 Season 2 Power BI Super Users!

Meet the 2020 Season 2 Power BI Super Users!

Find out who's part of the program this season, and welcome the new Super Users.

August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Featured Data Story of The Month

Featured Data Story of The Month

All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.

Power BI Dev Camp - Developing with .NET Core

Power BI Dev Camp - Developing with .NET Core

Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5.

Top Solution Authors
Top Kudoed Authors