Allocate Discount per Sales order to lines of that sale order

DAX DAX Calculations
1

Hello,

I am trying to calculate the net sales (sales - discount) for each product per sales order, however the discount per sales order is one line item. Is there a way to allocate the discount per each item sold per the sales order?

Currently, my measure adds the total discounts (“Discount” product code) of all Sales Orders, whereas I would like the measure to sum the total of all the lines of the individual sales order, and then apply the discount % for that sales order to get the net sales per product, regardless of whether a product or Sales order is filtered.

Discount Allocation.pbix (28.4 KB)

image
image1177×638 37.2 KB

Thanks for your time.

1 Like
2

Hello @Juju ,
Thank you very much for posting your query in the forum.
I will try to solve your problem.
Regards,

1 Like
3

Hi @Juju,
I share the solution that I have found in Power Query to distribute the total discount of each order among its item lines.

let
    Source = Csv.Document(File.Contents("C:\Enterprise DNA\Forum\Allocate Discount per Sales Orders to lines of that sales order\Discount.csv"),[Delimiter=";", Columns=3, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Sales Order Number", Int64.Type}, {"Amount", type number}, {"Product Code", type text}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Changed Type",{"Sales Order Number", "Product Code", "Amount"}),
    #"Added TotalGrossAmount" = Table.AddColumn(
              #"Reordered Columns", 
              "TotalGrossAmount",
              (a)=>  List.Sum( 
                        Table.SelectRows( 
                        #"Reordered Columns", 
                        (b) =>  b[Sales Order Number] = a[Sales Order Number] and b[Product Code] <> "Discount" ) 
                    [Amount]),
               type number),
    #"Added TotaDiscount" = Table.AddColumn(
              #"Added TotalGrossAmount", 
              "TotalDiscount",
              (a)=>  List.Sum( 
                        Table.SelectRows( 
                        #"Added TotalGrossAmount", 
                        (b) =>  b[Sales Order Number] = a[Sales Order Number] and b[Product Code] = "Discount" ) 
                    [Amount]),
               type number),
    #"Filtered Rows" = Table.SelectRows(#"Added TotaDiscount", each ([Product Code] <> "Discount")),
    #"Added Discount" = Table.AddColumn(#"Filtered Rows", "Discount", each ([Amount] * [TotalDiscount]) / [TotalGrossAmount], type number),
    #"Added NetAmount" = Table.AddColumn(#"Added Discount", "NetAmount", each [Amount] + [Discount], type number),
    #"Removed Other Columns" = Table.SelectColumns(#"Added NetAmount",{"Sales Order Number", "Product Code", "Amount", "Discount", "NetAmount"})
in
    #"Removed Other Columns"

I hope it can help you.

Regards,

Discount.csv (249 Bytes)
Discount Allocation_JAFP.pbix (34.2 KB)

3 Likes
4

Wow awesome, thank you very much!

1 Like