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.
I am trying to get Power BI to calculate the weighted average Gross profit % (GP $/Sales$) by customer. I am doing a customer analsysis and i currently have my data setup to show the top 10 customers by brand. I am analyzing lbs sold and GP $ by customer, but i would also like to have a line chart in my data showing the weight avg GP % by month, by year that will update when i slice by customer. My data currently has the GP $ and Sales $ on every order that we received. I set up a column to calculate the GP% and i am currently able to have my line chart show the Avg GP% but its not the WEIGHTED Avg GP%. Could anyone help me with how I would set this up in my Power BI data? I just started using Power Bi so I appologize for the question. I appreciate any help someone can give me! Thank you!
@Nacujo wrote:
I am trying to get Power BI to calculate the weighted average Gross profit % (GP $/Sales$) by customer. I am doing a customer analsysis and i currently have my data setup to show the top 10 customers by brand. I am analyzing lbs sold and GP $ by customer, but i would also like to have a line chart in my data showing the weight avg GP % by month, by year that will update when i slice by customer. My data currently has the GP $ and Sales $ on every order that we received. I set up a column to calculate the GP% and i am currently able to have my line chart show the Avg GP% but its not the WEIGHTED Avg GP%. Could anyone help me with how I would set this up in my Power BI data? I just started using Power Bi so I appologize for the question. I appreciate any help someone can give me! Thank you!
I don't think you have to create a column, instead you can create a measure. If you'd like further suggestion, please post some sample data and expected output.
@Eric_Zhang - On my top 10 customers for each brand, i want my measure to look at what the total gross profit by month for the customer that is selected divided by the total sales $. The data for my Power BI dashboard is taken from an excel pivot table. Below is a picture of some sales data for one of my top customers Pacific Foods in December 2016. Currently, Power BI is taking an avg GP % of each sale and averaging those GP % against each other, so it shows for this customer in December 2016, that we had a GP % of 4%. However, if you add up all the GP for the month/Sales $, the GP % for the month of December 2016 for Pacific Foods SHOULD be -2%. I hope this is enough info to give me an idea how to set this up.
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 |
---|---|
104 | |
95 | |
80 | |
67 | |
62 |
User | Count |
---|---|
138 | |
107 | |
104 | |
82 | |
63 |