PBI,
I am looking to develop a score that takes into account weighted average of two fields, using % distribution based on a category with unique values. I have provided example to show what I am trying to accomplish.
Want to incorporate % distribution of send counts and received by 'Method', which contains four values:
- Mail, Phone, Email, and Text
| Measure | Mail | Phone | Email | Text | Total |
| Count | 22 | 20 | 20 | 58 | 120 |
| Sent Exp +/- | 100.0% | 105.8% | 90.3% | 122.7% | 110.3% |
| Receive | 10 | 14 | 3 | 14 | 41 |
| Rec. Exp +/- | 127.7% | 102.1% | 99.4% | 103.3% | 108.5% |
- Count - # sent by method
- Sent Exp plus/minus - sent above or below the expectation
- Receive - # received by method
- Receive Exp plus/mins - received above or below the expectation
I also want to apply multiplier based on whether the method is Mail or Digital (Phone, Email, or Text):
| | Multiplier |
| | Mail | Digital |
| Sent | 0.4 | 0.25 |
| Collect | 0.6 | 0.75 |
The distribution of sent & received are shown in the table below, with the weighted sent & received +/-:
| Measure | Mail | Phone | Email | Text | Total |
| Count Dist | 18.3% | 16.7% | 16.7% | 48.3% | 100.0% |
| Wgt. Sent +/- | 18.3% | 17.6% | 15.1% | 59.3% | 110.3% |
| Receive Dist | 24.4% | 34.1% | 7.3% | 34.1% | 100.0% |
| Wgt. Rec +/- | 31.1% | 34.9% | 7.3% | 35.3% | 108.5% |
| Total Score | 26.0% | 30.6% | 9.2% | 41.3% | 107.1% |
- Count Dist - distribution of count by method (eg. using Mail --> 22/120 = 18.3%)
- Wgt. Sent plus/minus - (Sent Exp +/- from first table) * (Count Dist)
- eg. using Phone --> (105.8% * 16.7%) = 17.6%
- Receive Dist - distribution of received by method (eg. using Email --> 3/41 = 7.3%)
- Wgt. Received plus/minus (Rec Exp +/- from first table) * (Receive Dist)
- eg. using Text --> (103.3% * 34.3%) = 35.3%
- Score (applying the multiplier based on either mail or digital method)
- FORMULA: (Wgt. Sent +/- * Sent Multiplier) + (Wgt. Received * Collect Multiplier)
- eg. using Mail --> (18.3% * 0.40) + (31.1% * 0.60) = 26.0%
- eg. using Phone --> (17.6% * 0.25) + (34.9% * 0.75) = 30.6%
Finally, the sum of all scores should yield the total score --> 26.0% + 30.6% + 9.2% + 41.3% = 107.1%
The output I want to get in Power BI is that 107.1% with all the calculations taken into account. I know power BI has its own weighted averages but I am unsure how to do it with differing distributions of both sent & received counts (of total) as well as multipliers based on different values within a category (mail or digital method?). Ultimately, I want the rows to look like this for each observation:
| Observation | Mail | Phone | Email | Text | Total |
| Row A | 26.0% | 30.6% | 9.2% | 41.3% | 107.1% |
| Row B | 23.2% | 45.1% | 10.1% | 34.3% | 112.7% |
| Row C | 30.4% | 27.2% | 11.5% | 36.6% | 105.7% |
Any help would be appreciated. Regards.
