Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
120 | |
101 | |
71 | |
61 |