Totals not working properly - product hierarchy

[lpost1214]

Customer

Product Type

Item

Value

Month 1

Month 2

Grand Total

Walmart

Cough Drop

A

Forecast

75

100

175

Walmart

Cough Drop

A

Open Orders

100

75

175

Walmart

Cough Drop

A

Difference

0

25

25

Walmart

Cough Drop

B

Forecast

125

150

275

Walmart

Cough Drop

B

Open Orders

110

180

290

Walmart

Cough Drop

B

Difference

15

20

35

Total of Difference

15

45

60

Difference formula = if( open orders > forecast, 0, forecast - open orders)

Customer

Product Type

Value

Month 1

Month 2

Grand Total

Walmart

Cough Drop

Forecast

200

250

450

Walmart

Cough Drop

Open Orders

210

255

465

Walmart

Cough Drop

Difference

0

0

0

**Desired Result

15

45

60

When I collapse item view to just product type, the difference formula is being applied to the totals rather than just totaling the item level detail. How can I achieve this?

[Ashish_Mathur]

Hi,

It looks like the first table is a visual (not the raw data).  Share the raw data table in a format that can be pasted in an MS Excel file and show the expected result.

[audreygerred]

I reformatted that data a bit. Ideally, you want a column with Date and have many rows, and also have Forecast and Open Orders as separate field. When you do this, you can make the difference field in PowerQuery (unless it is a field you are bringing in from your source, in which case - pivot it right along with open orders and forecast).

Here are the results - I get the 15 in month 1 and 25 in month 2 (it looks like for item A you have the difference as 20, but since open orders are greater than forecast, it should be 0):

Here's the M-Code I used in Power Query:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs4vTc9QcCnKL1DSUXIEYrf8otTkxOISINPcFEgY6hvqGxkYmSjF6mBR7V+QmqfgX5SSWgRSamCAV4MTqvGGRvjNd8Iw3xC/+WjOhzjHiHj3g/2LWz26803xG4/pfAtUDbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Product Type" = _t, Item = _t, Value = _t, Column1 = _t, Date = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Product Type", type text}, {"Item", type text}, {"Value", type text}, {"Column1", Int64.Type}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Amount"}}),
#"Pivoted Column" = Table.Pivot(#"Renamed Columns", List.Distinct(#"Renamed Columns"[Value]), "Value", "Amount", List.Sum),
#"Added Custom" = Table.AddColumn(#"Pivoted Column", "Difference", each if [Open Order] > [Forecast] then 0 else [Forecast] - [Open Order]),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Difference", Int64.Type}})
in
#"Changed Type1"