Dividing two measures (sums of two columns) and using a hierarchy drill down as the filter

[JRICHA30]

Hi,

I am trying to calculate percentages of attendance for different levels of the hierarchy. (Organisation -> function -> department -> team).

For this, there is two measures:

Total Attendees = SUM(Headcount[Attendees])

Total Headcount = SUM(Headcount[Headcount])

Attendance = Divide( [Total Attendees], [Total Headcount],0)

Headcount is always > 0 

but attendance can =0

However, lets say at the team level, there is 4 teams, with a total headcount of 20 people.

lets say only 2 teams have registered attendance, so the data looks like:

Org	Function	Department	Team	Headcount	Attendance
Org 1	Function 1	Department 1	Team 1	5	5
Org 1	Function 1	Department 1	Team 2	5	5
Org 1	Function 1	Department 1	Team 3	5	0
Org 1	Function 1	Department 1	Team 4	5	0

The outcome should be Team 1 & 2 = 100%, Team 3&4 = 0% department 1 = 50% 

However, the measure is ignoring teams with 0 attendees & giving 

Team 1 & 2 = 100%

Team 3 & 4 = Blank

Department 1 = 100%

Any help here will be much appreciated.

(filters are applied to a bar graph by drilling down into the orgnisation, with the columns above used. There are 9 functions, each with 6-20 departments, each with 1-10 teams), so you can see if there is 0 attendance, it can make a huge difference to the figure at the top, as their headcount is being ignored.

Clicking "show items with no values" on the graph enables to see blank attendance at team level, but still does not roll the headcount up to department level.

any help much appreciated!

[Ashish_Mathur]

Hi,

How can the Attendance (%) be 50% for Department1.  Should it not be 10/120 i.e. 8.33%?

[v-angzheng-msft]

Hi, @JRICHA30 

Unable to reproduce your situation, how did you get the percentage of department 1 to be 100%?

the above measures seem to work fine to me.

Could you please consdier sharing more details about it or a simple sample file without any sesentive information and drawing a simple picture to show your expected visual so that i may work out with a workaround?

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[JRICHA30]

I have delved into this further and looked at it as a table:

Org	Function	Department	Department Tier 2	Headcount	Sum Headcount	Sum Attendees	% Attendance	Attendance % (done from table)
ORG 1	Function 1	Department 1	Team 1	464	464	13	2.80%	2.80%
ORG 1	Function 1	Department 1	Team 2	11	11	1	9.09%	9.09%
ORG 1	Function 1	Department 1	Team 3	71		0	0.00%
ORG 1	Function 1	Department 1	Team 4	219		0	0.00%
ORG 1	Function 1	Department 2	Team 5	10	10	6	60.00%	60.00%
ORG 1	Function 1	Department 2	Team 6	47		0	0.00%
ORG 1	Function 1	Department 2	Team 7	207	207	89	43.00%	43.00%
ORG 1	Function 1	Department 3	Team 8	10		0	0.00%
ORG 1	Function 1	Department 3	Team 9	36	36	28	77.78%	77.78%
ORG 1	Function 1	Department 3	Team 10	14		0	0.00%
ORG 1	Function 1	Department 3	Team 11	5		0	0.00%
ORG 1	Function 1	Department 3	Team 12	1		0	0.00%
ORG 1	Function 1	Department 3	Team 13	20		0	0.00%
ORG 1	Function 1	Department 3	Team 14	43		0	0.00%
ORG 1	Function 1	Department 3	Team 15	6	6	5	83.33%	83.33%
ORG 1	Function 1	Department 3	Team 16	10	10	10	100.00%	100.00%
ORG 1	Function 1	Department 3	Team 17	14		0	0.00%

It seems that whenever it 'summarises', it outputs a blank when there is 0 attendees.

However, all it needs to do is output what is correct in the headcount column. I know how to force it to output a specific number, but it will not allow me to reference the column? I've checked the data-types (what feels like 1 million times) to ensure it is a number also.

[JRICHA30]

Just as an add in, i tried to use the "ALL" function, but this then does not allow the hierarchy drillthrough feature to filter the measure. essentially i want all rows with headcount to be totalled, within the selected filter range.

[JRICHA30]

Department Level (1-level above furthest drill down)Team Level (Furthest Drill Down)

Hi All,

I have copied a small sample of the data below.

To clarify, the furthest drill down (at team level) shows all the teams, but only 3 have a bar (and are correct, and can be compared with the % shown in the sample table.)

However at department level, the bar illustrates only using the 3 teams with attendance > 0. Giving 43/52 = 82.69%.

However it should be doing, 43/159 = 27%.

(I've attached images but not full ones due to sesnsitivty)

(For clarity, the value on the graph is a measure and not the calculated column below)

Function	Department	Department Tier 2	Period Year	Headcount	No. Attendees	Attendance %
Function 1	Department 1	Team 1	2122 P05	14	0.00	0%
Function 1	Department 1	Team 2	2122 P05	1	0.00	0%
Function 1	Department 1	Team 3	2122 P05	43	0.00	0%
Function 1	Department 1	Team 4	2122 P05	20	0.00	0%
Function 1	Department 1	Team 5	2122 P05	10	10.00	100%
Function 1	Department 1	Team 6	2122 P05	36	28.00	78%
Function 1	Department 1	Team 7	2122 P05	6	5.00	83%
Function 1	Department 1	Team 8	2122 P05	10	0.00	0%
Function 1	Department 1	Team 9	2122 P05	14	0.00	0%
Function 1	Department 1	Team 10	2122 P05	5	0.00	0%