Subtotals in Matrix

[mmills2018]

Hello,

i have the below matrix in power bi.  all the measures are set up correctly, but for each company (Company A and B), it is summing the % Female and not dividing female headcount by Headcount.  Any ideas why this is happening?  can i not use a matrix?

	Female Headcount	Headcount	% Female (female hc /Hc)
Company A	35	110	1.576722191
Group 1	5	20	0.25 (5/12)
Group 2	6	21	0.285714286
Group 3	7	22	0.318181818
Group 4	8	23	0.347826087
Group 5	9	24	0.375
Company B	47	45	4.442424242
Group 6	10	11	0.909090909
Group 7	10	15	0.666666667
Group 8	12	10	1.2
Group 9	15	9	1.666666667

[AlexisOlson · Accepted solution]

Why not more simply Female % = [Female Headcount] / [Headcount Measure]?

Your SUMX is telling it to add the ratios of the components together for each row in the filtered Sheet1 table.

[mmills2018]

sorry, one more question, for my measures the 'grand total' is not showing, any reasons why?

[AlexisOlson]

Probably because of your ISINSCOPE functions. Those make your measures return blank if they aren't at one of the two levels you've specified.

It's not clear to me why those are there at all, especially since you are using the same values for all of the levels you specify.

[mmills2018]

ahh got it, thanks!!!

[AlexisOlson]

How are each of the measures defined?

[mmills2018]

below are the measures:

Female Headcount:

SUMX (

FILTER ( Sheet1, Sheet1[Management Level] IN VALUES ( 'Snapshots (Prod)'[Management Level (groups)] ) ),

IF (

ISINSCOPE ( Sheet1[OpCo] ),

(Sheet1[Female Headcount]),

IF (

ISINSCOPE ( Sheet1[Platform]),

(Sheet1[Female Headcount] )

)

))

Headcount Measure = SUMX (

FILTER ( Sheet1, Sheet1[Management Level] IN VALUES ( 'Snapshots (Prod)'[Management Level (groups)] ) ),

IF (

ISINSCOPE ( Sheet1[OpCo] ),

(Sheet1[Headcount]),

IF (

ISINSCOPE ( Sheet1[Platform]),

(Sheet1[Headcount])

)

))

Female % = SUMX (

FILTER ( Sheet1, Sheet1[Management Level] IN VALUES ( 'Snapshots (Prod)'[Management Level (groups)] ) ),

IF (

ISINSCOPE ( Sheet1[OpCo] ),

(Sheet1[Female Measure]/Sheet1[Headcount Measure]),

IF (

ISINSCOPE ( Sheet1[Platform]),

(Sheet1[Female Measure]/Sheet1[Headcount Measure] )

)

))

[AlexisOlson · Accepted solution]

Why not more simply Female % = [Female Headcount] / [Headcount Measure]?

Your SUMX is telling it to add the ratios of the components together for each row in the filtered Sheet1 table.

[mmills2018 · Accepted solution]

that did it!! thanks so much!

[mmills2018]

sorry, the above in Female % for Group 1 is 5/20 = .25