Discount Allocation

[watje255_ju]

Hello, 

I want to calculate the sales revenue generate from each product, but the total discount is a line item. Does anyone know how I could allocate the total discount to each item? The discount is a standard % across all products (in this example it would be 76%, being total sales of 666/872). 

Perhaps a calculated column that returns the % discount per sales order, which could then be used in a measure to get the discount amount per line, and then net sales revenue can be calculated. (the measures for discount and net sales i can do if the calculated column is created). However I am not sure how efficient that would be, open to any suggestions!

The requested result is per the 2nd screenshot below (first is current data)

Thanks in advance!

Link to PBIX file https://1drv.ms/u/s!As8wMr9_bgGrgWc5tGjeKEgdNZTH

[watje255_ju]

Hi @Avantika-Thakur , 

Thanks very much for your reply. 

Your reply works perfectly if there is only one sales order, but I have multiple sales orders so if I use ALL SELECTED the total discounts from all the sales orders are averaged over all the SKUS, whereas I would like to have the discount amount from the specific sales order to be allocated over the sales lines in the corresponding sales order. 

I will be using this to calculate the Net Sales and gross margin by SKU, so wont always have the SO Number in the table/graphic if that affects anything.

Is this possible? 

I have added another sales order line in the example PBIX file (same link/link below), with the desired result. 

Thanks for your time, 

Jessie

https://1drv.ms/u/s!As8wMr9_bgGrgWc5tGjeKEgdNZTH?e=yasyhr

[Avantika-Thakur · Accepted solution]

Hi @watje255_ju ,

You can try using the below measure for Discount calculation - 

Discount =

VAR DiscountAmount = calculate ([Sales Amount],

ALLEXCEPT(Discount,Discount[Sales Order Number]),Discount[Product Code]= "Discount" )

VAR SalesAmount = calculate ([Sales Amount], ALLEXCEPT(Discount,Discount[Sales Order Number]),Discount[Product Code]<> "Discount" )

Return DIVIDE(discountAmount,SalesAmount,0)

Hope this helps!

Please accept the solution if this answers your query.

Thanks!

Avantika