I am trying to calculate a weighted average discount by month across different years. I need somehow to first make the calculation sum the total sales by the month and then dividing the totals instead of each sale. I see what I do now makes a discount pr. sales line and then just makes an avg. of the percentages - but not weighted depending on sale size. Can anyone help how to limit the calculation to sum and divide totals?
What I would like to Avg Discount% = (Sum(GrossPrice by specific month) - sum(Netprice by specific month)) / Sum(GrossPrice by specific month)
What I've tried is Avg Discount% = IFERROR(1-(NetPrice/GrossPrice),0)
When I set this up in a matrix, I see it shows the avg. of the percentages used in the specific month instead of the weighted overall avg. of the totals.
Example of what I get and what I want
In the example I have simplyfied a lot, but what I get is the 53.3 instead of the 51.1. And then I also need the calculation to filter by month and year.
In order to find the weight you can use a calculated measure as such (probably not best practice, but will give you the idea).
Instead of using IFERROR when dividing, use the DIVIDE() function in DAX. It let's you choose an alternative value, if it can't divide.
If I have understood your question correctly, you want to calculate the SUM of the current row vs. the SUM of ALL the rows (the weight for each row, out of the total).
It would look something like this: (NOTE: I am using FILTER(ALL()) to remove all filters in the given table. this ensures that no matter what row you are calculating the values for, it will always take the TOTAL sum of all rows).
weight percent
I hope this is what you are looking for.