Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
Hi @watje255_ju ,
You can try using the below measure for Discount calculation -
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
Hi @watje255_ju ,
You can try using the below measure for Discount calculation -
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
19 | |
19 | |
15 | |
15 |
User | Count |
---|---|
49 | |
26 | |
22 | |
17 | |
16 |