Goal management assessment Power BI consolidation model

[mizaskun]

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. 

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:

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?

[Anonymous]

@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.

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

[mizaskun]

Thank you so much @Anonymous! 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! 

[Anonymous]

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.

[mizaskun]

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