Accumulate Measure within a table

[Anonymous]

Hello everybody,

I am quite new to working with PowerBI and right now I am trying to solve a bigger project where I would like to calculate the volume the products we get delivered. 

Therefor I would like to achieve the following table:

Articel	Number of deliveries	Avg. height of good per delivery	Substraction of deliveries	Height of goods
Cheese A	45	132	12 =(45-33)	132
Cheese B	33	119	14 =(33-19)	251
Snack A	19	105	5 =(19-14)	356
Snack B	14	69	14 =(same as number of deliveries)	425

The columens with the green text I already managed to calculate with an calculated measure for each column. Hower the last the column I cant manage to calculate correctly.

Let me try to explain what I want to achieve:

• Column: Substraction of deliveries
  • I would like to substract the result of the measure "Number of deliveries" by the value of the row below the measure.
    • For Example: Number of Deliveries of Cheese A - Number of Deliveries of Cheese B
  • In the last row I simple want the same value as the Measure "Number of Deliveries" for this item
• Column: Height of goods
  • Here I would simply like to accumulate the measure "Avg. height of good per delivery"
    • I already tried calculating this witha quick-measure however it doesnt seem to work. It always gives me the same result as Avg. height of good per delivery
    • It looks like this:

    • Articel	Number of deliveries	Avg. height of good per delivery	Substraction of deliveries	Height of goods
      Cheese A	45	132	12 =(45-33)	132
      Cheese B	33	119	14 =(33-19)	119
      Snack A	19	105	5 =(19-14)	105
      Snack B	14	69	14 =(same as number of deliveries)	69

Could anyone please tell me how I can get the needed results? Sadly I am out of any ideas for "Substraction of deliveries" since I do not know how to access the value from a different row. Also I am not sure why my Quickmeasre with the simple Accumulation of Avg. height of good per delivery doesnt seem to work. For some reason it doesnt allow me to select the measure as "field".

Thank you a lot in advance! If additional information is needed I will gladly share it.

[v-jingzhang]

Hi @Anonymous 

Maybe you have solved this problem. If so, can you share your solution here?

If not, my idea is that you could add an index column for Articels so that each Articel has a unique index number. Then we can use index number to refer to a specific articel to get its value. You can add an Index column in Power Query (Add column > Index column). Or if there are not many articels, you can also use DAX's SWITCH function to do it. And you get it like below

Articel	Index
Cheese A	1
Cheese B	2
Snack A	3
Snack B	4

Then create a simple measure for number of deliveries, e.g. Number of deliveries = COUNT('tablename'[deliverycolumn])

I use COUNT for example as I don't know much about your data. You can change it per your need. 

And create another measure to get its next row data.

Number of deliveries next =
VAR __index =
SELECTEDVALUE ( 'tablename'[Index] )
RETURN
CALCULATE (
[Number of deliveries],
ALL ( 'tablename' ),
'tablename'[Index] = __index + 1
)

At last, create the third measure to get the result. 

Substraction of deliveries = [Number of deliveries] - [Number of deliveries next]

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[Anonymous]

Funny, I happen to be working on yet another method to do row math with the previous/next row.  See how you like this one:

let
Source = Table,
Custom1 = List.Zip({Table.Split(Source, 2), Table.Split(Table.Skip(Source), 2)}),
#"Converted to Table" = Table.FromList(Custom1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandListColumn(#"Converted to Table", "Column1"),
#"Added Custom" = Table.AddColumn(#"Expanded Column1", "Current", each List.First([Column1][Number of deliveries])),
Custom2 = Table.AddColumn(#"Added Custom", "Prior", each try [Column1]{1}[Number of deliveries] otherwise 0),
#"Changed Type1" = Table.TransformColumnTypes(Custom2,{{"Current", Int64.Type}, {"Prior", Int64.Type}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type1", "Difference", each [Current] - [Prior], Int64.Type),
Custom3 = Table.TransformColumns(#"Added Custom1", {{"Column1", Table.First}}),
#"Removed Columns" = Table.RemoveColumns(Custom3,{"Current", "Prior"}),
#"Expanded Column5" = Table.ExpandRecordColumn(#"Removed Columns", "Column1", {"Articel", "Number of deliveries", "Height of goods"}, {"Column1.Articel", "Column1.Number of deliveries", "Column1.Height of goods"})
in
#"Expanded Column5"

So basically, we are splitting the table at each 2 rows.  Then we skip one row of the original table, adn split that table every two rows.  This give you two lists of tables, one with rows 12, 34, 56, 78 and another list of rows 23, 45, 67, 89.  Since these are lists, we can combine them back together by position instead of row number using List.Zip. 

There are many, many other ways to do this though.  This is jsut a new one (for me, anyway).

--Nate

[Anonymous]

Hello!

I just managed to fix the second problem and therefor edited the post! If somebody could help me out how I could achieve the measure "substraction of deliveries" as shown in the table that would be a huge help!

Best regards!