Forum Discussion
Weighted Average by Category + Distribution using Multiple Fields
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 | Phone | 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 | ||
| 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 | Phone | 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%
- FORMULA: (Wgt. Sent +/- * Sent Multiplier) + (Wgt. Received * Collect Multiplier)
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 | Phone | 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.
3 Replies
- kentyler
Solution Sage
It looks like you are visualizing your data structure with the structure of the report you are aiming at. You might need to unpovit the data like this
- kentyler
for example
count sent by method becomes a simple sum
but in the unpivoted data you have to sum up and combine a count for each method
