Help - How to Sum Scores and Count Unique People Results??

[karimk]

Hi everyone,

I need to make a donut chart that shows the amount of people per Result Category, but I´m stuck with the tables and data I have. 

Table A shows people, their goals, the weight of each goal and the result. It can have more than one row per person (more than one goal per person). Example below:

Each result corresponds to a number. Example below:

If I multiply the corresponding point by the weight, I get the Score.

If the SUM(score) of each unique person is <2, then it gets a final score of "Not Enough". If it´s ❤️ and >=2, then it´s "On Point". If it´s >=3, then it´s "Outstanding".

I need to know how many unique people have a final score of "Not Enough", "On Point" and "Outstanding".

Here is a dataset example link: OneDrive Link

[v-xicai · Accepted solution]

Hi @karimk ,

You can create column and measure like DAX below.

Column:

Final score =
var Score = Table1[Weight]*RELATED(Table2[Point])
var Sum_score= CALCULATE(SUMX(Table1,Score),FILTER(Table1,Table1[Person]=EARLIER(Table1[Person])))
return
SWITCH(TRUE(), Sum_score <2,"Not Enough",Sum_score>=2&&Sum_score<3,"On Point",Sum_score>=3,"Outstanding")
 

Measure:

Count = CALCULATE(DISTINCTCOUNT(Table1[Person]),FILTER(ALLSELECTED(Table1),Table1[Final score]=MAX(Table1[Final score])))

Result:

You can download my test pbix file .

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[karimk]

Thank you very much @v-xicai 

Last question: If any of the result lines for a given person are blank, I want to classify that person as "Has not Filled Report". This is a condition to come before the Sum_score.

Example:

In the table above I would get a final score of:

On Point = 2 people

Has not Filled Report = 1 person

The updated dataset is here: Onedrive Link

[danextian]

You'll get a response faster if you provide a dummy data.

[karimk]

 Thanks for the tip. I have put a link at the end of my original post.

[danextian]

Hi @karimk,

By sample/dummy data, I meant raw data that still needs to be processed and not your desired output.