Forum Discussion

watje255_ju's avatar
watje255_ju
Icon for Helper III rankHelper III
4 years ago
Solved

Discount Allocation

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 discoun...
  • Avantika-Thakur's avatar
    Avantika-Thakur
    4 years ago

    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
watje255_ju's avatar
watje255_ju
Icon for Helper III rankHelper III
4 years ago

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