cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mizaskun
Helper I
Helper I

Goal management assessment Power BI consolidation model

Good morning,

 

I am working on a goal management dashboard in which some company goals are assessed by different people. There are three levels of goals, and the assessments must be consolidated from the lower level to the highest level. 

mizaskun_0-1620646915316.png

 

The L1 level contains all the goals completely consolidated. Every goal from L2 or L3 must have a L1 "parent" goal, in which will finally consolidate.

The L2 level contains a disaggregation of certain L1 goals. It is a subgoal of the L1 level ones. 

The L3 level contains a disaggregation by country of either L1 or L2 goals. 

 

In the above graph each goal have a A or a C.

  • A: Assessed goal. This goal is assessed by one or many people. Thus, it will appear in the input table.
  • C: Consolidated goal. The goal is calculated from all the subgoals that are in lower levels. A C goal can be the calculation of some A goals and some C goals.

 

Considering the 3 goals of the below image, I have created an adhoc assessment table for this example:

Goal

Value

RA EUR

2

RA EUR

5

RA EUR

4

RA IND

7

RA IND

5

RB

2

RC.01

3

RC.01

1

RC.02 EUR

4

RC.02 EUR

3

RC.02 IND

2

RC.02 IND

6

RC.02 IND

4

RC.02 IND

7

RC.02 IND

4

RC.02 IND

5

RC.02 USA

2

RC.02 USA

1

 

In addition, each region has a weight for the calculation:  

Region

Weight

EUR

5

IND

3

USA

2

 

For example, the RA goal is calculated as shown below:

 

The L3 is calculated with a normal average:

RA EUR: (2 + 5 + 4) / 3 = 3,66

RA IND: (7 + 5) / 2 = 6

 

And it is consolidated in the L1 RA considering the weights:

Region

Weight

% weight

Assessment

Weighted assessment

EUR

5,00

0,63

3,66

2,29

IND

3,00

0,38

6,00

2,25

 

 

 

total

4,54

 

The RA goal has a 4,54 ponderation in total.

 

The RB goal is a normal average of the assessments.

 

The RC goal is calculated as shown below:

Region

Weight

% weight

Assessment

Weighted assessment

USA

2,00

0,20

1,50

0,30

EUR

5,00

0,50

3,50

1,75

IND

3,00

0,30

4,66

1,40

 

 

 

total

3,45

 

This is the final calculation schema:

mizaskun_1-1620647211303.png

 

 

What I need is a report in which I can drill down from the L1 to the L3 obtaining details of the calcs.

 

I have to come up with the model. Thus, I can implement almost any needed table. 

 

Does anyone know how to achieve this?

 

4 REPLIES 4
daxer
Solution Sage
Solution Sage

@mizaskun 

 

I did this rather qickly. The measures should be re-written in such a way that there's no code duplication. Right now L1 Goal Value and L2 Goal Value have some duplicated code that should be moved to a helper function (which should be hidden). All the goals across all the levels should have unique names (if they don't, then some calculations might be wrong but such a case could also be handled at the cost of some additions to the model or more complex DAX).

 

Testing belongs to you, my friend.

 

daxer_0-1620731713895.png

daxer_1-1620731828266.png

daxer_2-1620731890024.png

The empty cells in all the tables contain BLANKS, never empty strings.

daxer_3-1620731947036.png

daxer_4-1620732003941.png

daxer_5-1620732134705.png

daxer_6-1620732321455.png

daxer_7-1620732495913.png

 

 

Thank you so much @daxer! I will have to analyze this in detail. It would be really nice if I could get the pbix file to do so easier. Thank you so much again! I will write back in the post as soon as I analyze and understand it! 

Can't give you a file as I'm writing from work and the policy prohibits this. Also, the controls on the page didn't want to work, so I had to paste pictures instead of formatted code. But you can easily copy the code by hand. I'm sure it's not too high a price to pay for the solution.

Thank you! I will work on this and write you back soon with my feedback. Regards! 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates