Re-calculate percentage when drilling between levels

[DaneM123]

I'm currently working with some data on the number of organisations using a piece of software, and need to be able to plot its use over time with the ability to drill between Region and Subregion level (a basic version of the data is copied below; Total refers to the number of organisations in the subregion). I added a measure to calculate the percentage or organisations live, but the problem I have is that when I go up to Region level, rather than re-calculating the percentage for the region it just gives an average of the percentages for the individual subregions which obviously gives the wrong answer. How would I go about getting it to re-calculate the percentage live at each level so I can get the correct figure?

Region	Subregion	Date	Total	Live
A	A1	JAN	10	3
A	A1	FEB	10	6
A	A1	MAR	10	9
A	A2	JAN	5	2
A	A2	FEB	5	4
A	A2	MAR	5	5
B	B1	JAN	10	3
B	B1	FEB	10	6
B	B1	MAR	10	9
B	B2	JAN	5	2
B	B2	FEB	5	4
B	B2	MAR	5	5

[v-yuezhe-msft]

@DaneM123,

What is your expected result in subregion level and region level? What formula do you use to create the percentage?

Regards,
Lydia

[DaneM123]

Thanks for getting back to me.

At the moment I'm just using a really basic percentage calculation, i.e. number live/total, which works fine for the individual rows. So using January's data as an example, for subregion A1 it gives 30% live (3 live out of 10 organisations) and for A2 it is 40% (2 out of 5).

When moving up to Region level, for region A this should give a result of 33.3% live, i.e. 5 organisation live out of 15. However, because it takes the average of the results from the two subregions, it actually gives a result of 35% ((30+40)/2).

[v-yuezhe-msft · Accepted solution]

@DaneM123,

Create the following measure in your table.

Measure = SUM(Table[Live])/SUM(Table[Total])


Regards,
Lydia Zhang

[DaneM123]

That's worked - thanks so much. I knew it would turn out to be somethign really simple!