Dear Experts,
I have a list of 225 accounts and i need to score each account out of 100, based on its % achievement of target, % forecast accuracy and funnel coverage ( which indicates how much funnel is left in the system to sell the reminder of target/quota).
I have created 3 measures to calculate % achievement, % forecast accuracy and funnel coverage.
The data would look like below :
| Global Client Name | Client Manager | % Achieved | % Forecast-Accuracy | Funnel Coverage | Final Score out of 100 |
| Honda Motor Co., Ltd. | Satoshi Isoda | 12.50% | 200% | 1.2 | |
| Kasikornbank Group | Alisa Chamnianlap | 15.56% | 93% | 5.2 | |
| Nissan Motor Co., Ltd. | Satoshi Isoda | 4.06% | 100% | 3.1 | |
| Accident Compensation Corporation | Greg Brown | 2.91% | 98% | 4.2 | |
| SUMITOMO MITSUI FINANCIAL GROUP, INC. | Keita Nomura | 7.09% | 50% | 2.0 | |
The weightage that we wants is, % achievement would carry 50% weightage of total score ( i.e. since i need to score out of 100, % achievemnt would carry a maximum score of 50 ) like wise, % forecast accuracy would carry 25% and Funnel coverage would carry another 25%.
The table below discribes how i want to score
| Attainment % | Score | Forecast Accuracy % | Score | Funnel Coverage Ratio | Score | Final Score |
| >=100% | 50 | >=100% | 25 | >=5 | 25 | 100 |
| >=85% | 40 | >=85% | 20 | >=4 | 20 | 80 |
| >=60% | 30 | >=60% | 15 | >=3 | 15 | 60 |
| >=50% | 20 | >=50% | 10 | >=2 | 10 | 40 |
| >=30% | 10 | >=30% | 5 | >=1 | 5 | 20 |
| <30% | 0 | <30% | 0 | <1 | 0 | 0 |
I need your help to come up with a DAX to arraive at a Final Score for an account. I will be craeting a Matrix visual to show all the data with a date sclicer. I do have a seperate calendar table in my data model.
Thanks in advance for all your help and support.
Krishna
