Forum Discussion
Average with Limited Value
Hi,
I need to be able to visualize the percentage of meeting a goal of the various departments of the company over the months.
The problem is that there are some difficulties, below are the rules:
1. A department can have a varied number of people among them.
2. Each person is a role.
3. The goal is the same for all people.
4. Goal achievement is calculated as follows:
- If a person reached the goal or exceeded it then he reaches 100%, if he did not reach it, then the % is equal to what he did / goal.
- Meeting the department's goal is the average of these values.
That is, it's no use for me to add the total of records and divide, I need to calculate the % of each function and only then do the average.
5. Each row in the worksheet is a record. The goal is based on this number of records.
6. Data needs to be visualized without showing functions.
| January | February | |||||||||||
| Report | Date | Function | Department | Financial | Reports | Achieved Goal | Goal Considered (Limited to 100% no matter if the achieved goal is greater than 100%) | Reports | Achieved Goal | Goal Considered | ||
| 1 | 01/01/2022 | Manager | Financial | Manager | 2 | 100% | 100% | 2 | 100% | 100% | ||
| 2 | 02/01/2022 | Manager | Financial | Director | 1 | 50% | 50% | 1 | 50% | 50% | ||
| 3 | 03/01/2022 | Director | Financial | Intern | 2 | 100% | 100% | 2 | 100% | 100% | ||
| 4 | 04/01/2022 | Intern | Financial | Marketing | ||||||||
| 5 | 04/01/2022 | Intern | Financial | Manager | 3 | 150% | 100% | 4 | 200% | 100% | ||
| 6 | 01/01/2022 | Director | Marketing | Director | 2 | 100% | 100% | 2 | 100% | 100% | ||
| 7 | 02/01/2022 | Director | Marketing | |||||||||
| 8 | 03/01/2022 | Manager | Marketing | |||||||||
| 9 | 04/01/2022 | Manager | Marketing | |||||||||
| 10 | 04/01/2022 | Manager | Marketing | |||||||||
| 11 | 01/02/2022 | Manager | Financial | January | February | |||||||
| 12 | 02/02/2022 | Manager | Financial | Achieved Goal | Achieved Goal | |||||||
| 13 | 03/02/2022 | Director | Financial | Financial | 83% | 83% | ||||||
| 14 | 04/02/2022 | Intern | Financial | Marketing | 100% | 100% | ||||||
| 15 | 05/02/2022 | Intern | Financial | |||||||||
| 16 | 06/02/2022 | Director | Marketing | |||||||||
| 17 | 07/02/2022 | Director | Marketing | |||||||||
| 18 | 08/02/2022 | Manager | Marketing | |||||||||
| 19 | 09/02/2022 | Manager | Marketing | |||||||||
| 20 | 10/02/2022 | Manager | Marketing | |||||||||
| 21 | 11/02/2022 | Manager | Marketing | |||||||||
1 Reply
Hi areias_br
Thanks for reaching out to us.
This looks like it should be the data in the matrix and appears to be the calculated data. Can you provide a .pbix file containing the raw uncomputed data? And please provide the formula how to calculate it, when a person reached the goal or exceeded it, formula=100%; when he did not reach it, formula= ? (need examples according to sample data)
Thanks.
Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
