Forum Discussion
Problem with Calculation - Average per person per group
The calculations are coming out as programmed, but it is not what I want for the final result. I don't know how to create the measure to give me the desired result:
Dr. Name | Last Name | Sum of Risk Score | # Claims | Risk Score |
Stars | Monds | 4151.38 | 539 | 7.70 |
Stars | Anderson | 198.27 | 129 | 1.54 |
Stars | Chambers | 447.89 | 344 | 1.30 |
Stars | Quarles | 23.19 | 34 | 0.68 |
Total |
| 4820.73 | 1046 | 4.61 |
What I want is Risk Score in the total line to be (7.7 + 1.54 + 1.30 + .68) / 4 = 2.805
Dr. Name | Last Name | Sum of Risk Score | # Claims | Risk Score |
Grinds | Wallace | 1170.05 | 350 | 3.34 |
Grinds | Gant | 115.71 | 256 | 0.45 |
Grinds | Jones | 0.00 | 19 | 0.00 |
Total |
| 1285.76 | 625 | 2.06 |
What I want is Risk Score in the Total line to be (3.34 + 0.45 + 0.00) / 3 = 1.263
Summary Table (my final goal)
Dr. Name | #Users | Sum of Risk Score | # Claims | Risk Score | Risk Score (I want) |
Stars | 4 | 4820.73 | 1046 | 4.61 | 2.805 |
Grinds | 3 | 1285.76 | 625 | 2.06 | 1.263 |
Total | 7 | 6106.49 | 1671 | 3.65 | 2.144 |
2.144 = (7.7 + 1.54 + 1.30 + 0.68 + 3.34 + .45 + 0.00) / 7
I want to show the total Risk Score for each dr. to reflect the above values of “What I Want”
Ie: I want Stars to have a Risk Score of 2.805 and Grinds to have a risk score of 1.263. I want the Total Risk Score to be the sum of all patients individual risk score divided by the # of patients.
How do I do this calculation? The individual risk scores for each person is correct. Technically the risk score calculation total for each doctor is correct, but I don’t want to take into account the # of claims, etc. I just want the sum of the individual patient’s risk score divided by the # of patients.
Thank you for your time and consideration.
Valerie
1 Reply
This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376.
Also, have a look at my Matrix Measure Total Triple Threat Rock & Roll Quick Measure here:
