Build a hierarchical Matrix with calculated fields

Anonymous — Mon, 10 May 2021 16:39:51 GMT

Hello,

I'm having some trouble building a matrix in Power Bi. I have two related tables (one for data and one for KPIs), and I want to calculate what's the % below target for each Manager and Division.

However, I need to show the exact percentage for each Division and Manager in a matrix comparing the % below target against each of their KPIs in one column (hierarchical matrix). Note that manger results don't necessarily represent the results for the division.

Data numbers are just examples.

	TABLE 1
Division	Manager	Results
North	John	3
North	Paul	4
East	Mike	2
East	Lois	5
East	Pete	6
West	Karen	2
South	Nick	7
South	Chris	9
South	Frank	1
South	Mel	6

		TABLE 2
Division	Manager	Division Target	Manager Target
North	John	12	5
North	Paul	12	8
East	Mike	15	10
East	Lois	15	7
East	Pete	15	9
West	Karen	3	5
South	Nick	25	8
South	Chris	25	13
South	Frank	25	2
South	Mel	25	7

RESULTS MATRIX
	% Below Target
North	42%
John	40%
Paul	50%
East	13%
Mike	80%
Lois	29%
Pete	33%
West	33%
Karen	60%
South	8%
Nick	13%
Chris	31%
Frank	50%
Mel	14%

Thank you for your support.

Re: Build a hierarchical Matrix with calculated fields

v-lionel-msft — Wed, 12 May 2021 02:02:33 GMT

Hi Anonymous ,

I don't particularly understand the mathematical calculation logic of this measure, please give an example to explain it.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

topic Build a hierarchical Matrix with calculated fields in Developer

Build a hierarchical Matrix with calculated fields

Re: Build a hierarchical Matrix with calculated fields