Forum Discussion
Weighted Average with calculated weights
Hello again PBI community, thank you for the responses to my last post. I will test and confirm answers on Monday 
I have quite a good problem to solve. I have 3 tables.
The first table contains IDs, start and end dates, and a value.
The second and third tables are date tables, one linked to the start date, and one linked to the end date. (The purpose of this is so users can select any dates in the date slicer, not just the ones that happen to have data in the first table).
I have it set up so the user selects a range based on 2 slicers, the first range is a greater than date based on the end date, and the second is a less than date based on the start date. The purpose here is so the user can select any date range, and find the ID's that were 'live' in that range. Live means that if the selected dates are [end date>02/05/2018] and [start date<02/10/2018] all ID's that have overlap in this period (the whole period or part of the period) will show up.
The period created is 02/05/2018 to 02/10/2018. So any combination of being live for 1 day to all days in the selected period would show up.
Here is an example of the tables, and where the problem comes in.
Table 1:
When the range of 02/05/2018 - 02/10/2018 is selected here is where I can get to:
*Note: the days are inclusive so the entire period of being 'live' is 6 days.
The trouble is that the days in the period selected is a measure based on the slicer selections, the weights are also a measure, and I cannot divide the weights by the sum of the days to get the weighted amount. This is really where I need help.
I included a category because there is a heirarchy of categories, so weighted averages within categories is what I need. This is also just an example. There are hundreds of categories, and tens of thousands of ID's, so no formulas with non-dynamic grouping are feasible.
If further explanations are needed then I will definately put some more color around the problem, just send post asking what I am missing!
9 Replies
So, in the example provided what should the Weighted Average numbers be? 50,000, 60,000 and 70,000 or something different and if different, what?
The weighted average for category A should be the weights of each item [(6 * 50,000) + (5 * 60,000)] / total days in category (11) = 54,545. The weighted average is not filled in yet in my screen shots because I can't figure out the calculation. The blue fields filled in are what I am able to do so far. Having the days in period respond to the slicer selections is throwing me for a bit of a loop.
Thank you for the clarifying question, and any help from the community would be much appreciated :)
Hello again PBI community, thank you for the responses to my last post. I will test and confirm answers on Monday :)
I have quite a good problem to solve. I have 3 tables.
The first table contains IDs, start and end dates, and a value.
The second and third tables are date tables, one linked to the start date, and one linked to the end date. (The purpose of this is so users can select any dates in the date slicer, not just the ones that happen to have data in the first table).
I have it set up so the user selects a range based on 2 slicers, the first range is a greater than date based on the end date, and the second is a less than date based on the start date. The purpose here is so the user can select any date range, and find the ID's that were 'live' in that range. Live means that if the selected dates are [end date>02/05/2018] and [start date<02/10/2018] all ID's that have overlap in this period (the whole period or part of the period) will show up.
The period created is 02/05/2018 to 02/10/2018. So any combination of being live for 1 day to all days in the selected period would show up.
Here is an example of the tables, and where the problem comes in.
Table 1:
When the range of 02/05/2018 - 02/10/2018 is selected here is where I can get to:
*Note: the days are inclusive so the entire period of being 'live' is 6 days.
The trouble is that the days in the period selected is a measure based on the slicer selections, the weights are also a measure, and I cannot divide the weights by the sum of the days to get the weighted amount. This is really where I need help.
I included a category because there is a heirarchy of categories, so weighted averages within categories is what I need. This is also just an example. There are hundreds of categories, and tens of thousands of ID's, so no formulas with non-dynamic grouping are feasible.
If further explanations are needed then I will definately put some more color around the problem.
