Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
watje255_ju
Helper II
Helper II

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 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_1-1658485102742.png

 

 

watje255_ju_0-1658485054734.png

 

1 ACCEPTED SOLUTION
Avantika-Thakur
Solution Supplier
Solution Supplier

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)
AvantikaThakur_0-1658486786511.png

 

Hope this helps!
 
Please accept the solution if this answers your query.
Thanks!
Avantika

View solution in original post

2 REPLIES 2
watje255_ju
Helper II
Helper II

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

watje255_ju_0-1658637029580.png

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

Avantika-Thakur
Solution Supplier
Solution Supplier

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)
AvantikaThakur_0-1658486786511.png

 

Hope this helps!
 
Please accept the solution if this answers your query.
Thanks!
Avantika

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors