Average with Limited Value

[areias_br]

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

[v-xiaotang]

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.