Hi there, as usual I have searched for this across the forum so apologies if it has been answered elsewhere but I can't find it.
I suspect this is more of a stastistics question than a Power BI one, but as I am using Power BI I'll need to write it in DAX anyway.
The basic premise is:
The company I work for measures total instances and outcomes. To simplify things, these outcomes are 'Good' and 'Bad'. We then calculate a 'Good outcomes rate', i.e. Good outcomes/instances. This rate is tracked over numerous locations and services, so we look at things such as the YTD Good outcomes rate vs the 5 year mean Good outcomes rate, displayed as percentage point differences. 5 year mean in this case is the previous 5 years, not including the current year.
E.g.
| 2021/22 YTD GOR | 5 YM GOR | % Point diff |
| 73.5% | 76.9% | -3.4 |
This is where it gets tricky, because in reality we are measuring this rate across hundreds of categories (and indeed subcategories), with varying numbers of instances (i.e. some have a high number of instances, some very few). As a mocked up example:
| Category | Instances | Good outcomes | GOR |
A | 298 | 200 | 67.1% |
| B | 2001 | 1820 | 91.0% |
| C | 4500 | 3679 | 81.8% |
| D | 972 | 769 | 79.1% |
| E | 109 | 98 | 89.9% |
| F | 15 | 3 | 20.0% |
| G | 2270 | 2104 | 92.7% |
| H | 1865 | 1699 | 91.1% |
| Total | 12030 | 10372 | 86.2% |
As you can see, with this set up you may get quite big fluctuations in individual categories. What I want to calculate is a way of accounting for the differences in volume of incidents/good outcomes, so that I can understand how much each category actually contributes to changes in the overall rate. So, if the overall good outcome rate has decreased by 3 percentage points, I would like to establish that -0.4 of that change came from category G, and category F contributed -0.9 etc.
Obviously we can tell, roughly speaking, what is contributing the most to changes in the rate (i.e. on the table above, we would pay more attention to categories C and G than F or E), but it would be amazing to have an automated process that could be plugged into a decomposition tree, with 'Change in GOR from the 5 year mean' as the value and the categories as the layers beneath, so that people in the organisation could see, numerically, which areas/categories were important/relatively unimportant.
I hope this makes sense, thanks in advance if anyone can help.
