Calculated Column with row information

DAX DAX Calculations
1

Hello everyone,

I hope you guys can help me! Please see this image. I would like to calculate the total paid amout per item after discount. For example: Item Nr. 001234 is 80 with a discount of 24. (This was a 30% discount code). The answer is $ 56 of course. Is there a DAX available when:

If in column “Discount Code” “Bonkorting” is applied, fill in the column “Item Nr.” the Item Nr. from the row above. So in this example Item Nr. 001234 in orderline 20000 and 001231 in orderline 40000 etc. etc. etc.

Naamloze afbeelding
Naamloze afbeelding990×263 66.8 KB

Looking really forward for your helps guys! Or maybe you have a another solution!

Best Regards,

Nick

2

Hi @NickvanDijk. Please post your work-in-progress PBIX (with sanitized or sample data, if necessary) for the forum members to better understand your ask. (in the image you provided, we can’t see the correspondence with your 001234 example). As well, please provide an Excel mockup of exactly the result you’re looking for.
Greg

3

Hello Greg,

Thank you for you fast response. Underneath you find a sample pbix file aswell the Excel file what I would like to achieve. I you have further questions please let me know.

sales sample.xlsx (10.8 KB) sales sample.pbix (26.0 KB)

4

Hi @NickvanDijk. Thanks for the files, but its still unclear to me what you’re looking for: all of your missing values in the [Item Nr.] column in your sample should be able to be easily added during load using the Fill down command in Power Query. I’m guessing I’m missing something, so could you perhaps add a visual to your PBIX and include the values you’d like shown, and then screen-shot that visual and mark it up with exactly what you’d like to see?
Greg

5

Hi @NickvanDijk,

Paste this into a new blank query and see if that meets your requirement.
This solution assumes a discount value always directly follows the item line it should be applied to.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nVTbCoMwDP0Xnx00Ta+vYzLGQEHGBhP//zdWbQdrUqVO8Mo5J8lJ4jQ1rwH8SSgrRdM2IMIR7stpbLgs30A0c0uAMgHxCzwP/X0YH7f+Gl5OiJyCiSLD+ezGd9dfYgAiDyQPkMZHoC0g5Q8S1zjLg+bA2vCShgedwgMWoLJSFqm7sOEuUneBuas1p9SWp2h5uNVmRRKJyDwT41qrOa02GU2SQXDlBuo9p03rCdywEUIVsU5wZFZkROZVhpoYCXP51E1TkFe5PG9nKJqRdL4AdlveZPKFXSzJ22p594+8rzZn7dJhdwAqB8yyZQaTxp1Ogj2wzI7JCrWxRe6ArK//9Xg6txFK/sKek/ZWc/4A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Document Nr." = _t, Orderline = _t, Type = _t, #"Item Nr." = _t, #"Discount Code" = _t, #" Line Amount " = _t]),
    ChType = Table.TransformColumnTypes(Source,{{"Document Nr.", type text}, {"Orderline", Int64.Type}, {"Type", Int64.Type}, {"Item Nr.", type text}, {"Discount Code", type text}, {" Line Amount ", type number}}),
    AddIndex1 = Table.AddIndexColumn(ChType, "Index", 0, 1, Int64.Type),
    AddIndex2 = Table.AddIndexColumn(AddIndex1, "Index.1", 1, 1, Int64.Type),
    AddCustom = Table.FillUp( Table.AddColumn(AddIndex2, "Custom", each if ChType[Type]{[Index]} = 0 and ChType[Type]{[Index.1]} =3 then null else [Index] ), { "Custom"}),
    AddNettAmt = Table.AddColumn(AddCustom, "Nett Amt", each if [Type] =0 then List.Sum( Table.SelectRows( AddCustom, (IT)=> IT[Custom] = [Custom] )[#" Line Amount "] ) else if [Type] =3 then null else [#" Line Amount "], type number),
    RemoveColumns = Table.RemoveColumns(AddNettAmt,{"Index", "Index.1", "Custom"})
in
    RemoveColumns

.

I hope this is helpful.

6

Hello @Melissa this solution is awesome. Thank you so much for this! The M-code is for me “next level” so I’m really gratefull you took the time to help me with my problem. Lots of love!

7

Hello @Greg sometimes there is an easy solution and I’m thinking to difficult. The Fill Down command worked for me after I replaced the blank values in “null”. Thank you a lot for spending your time on my problem.