Wrong % on 100% Stacked Bar Chart??

[deepvibha]

Hi,

I have a base table in Excel like below:

Month	Hospital	HospType	Total Revenue	Insurance Revenue
Feb-16	CBE	Tertiary	32305362	6058119
Feb-16	DGL	Secondary	2620280	554956
Feb-16	MDU	Tertiary	60597939	18332756
Feb-16	PONDY	Tertiary	24391223	7975557
Feb-16	SALEM	Tertiary	7822501	1717305
Feb-16	THENI	Secondary	4876369	792093
Feb-16	TUP	Secondary	1508351	105822
Feb-16	TUT	Secondary	2479742	4060
Feb-16	TVL	Tertiary	27575199	8373926
Feb-16	UPET	Secondary	1238410	157640
Mar-16	CBE	Tertiary	39132376	10090395
Mar-16	DGL	Secondary	2799581	556781
Mar-16	MDU	Tertiary	73565580	20315306
Mar-16	PONDY	Tertiary	26779986	9598620
Mar-16	SALEM	Tertiary	8971141	1936270
Mar-16	THENI	Secondary	5675946	1139493
Mar-16	TUP	Secondary	4261907	159590
Mar-16	TUT	Secondary	2467288	3960
Mar-16	TVL	Tertiary	35579700	9264903
Mar-16	UPET	Secondary	2163418	326724

which is imported in PBI like below:

I've created two measures like below:

Gross Reveune =
CALCULATE (
SUM ( RevenueMaster[Value] ),
RevenueMaster[Attribute] = "Total Revenue"
)

Gross Insurance =
CALCULATE (
SUM ( RevenueMaster[Value] ),
RevenueMaster[Attribute] = "Insurance Revenue"
)

I have plotted these measures on a "100% Stacked Bar Chart", which appears as follows:

The average "Insurance %" for the month of February-2016 in Excel works out to be 19% as shown below:

Month	Hospital	HospType	Total Revenue	Insurance Revenue	Insurance %
Feb-16	CBE	Tertiary	32305362	6058119	19%
Feb-16	DGL	Secondary	2620280	554956	21%
Feb-16	MDU	Tertiary	60597939	18332756	30%
Feb-16	PONDY	Tertiary	24391223	7975557	33%
Feb-16	SALEM	Tertiary	7822501	1717305	22%
Feb-16	THENI	Secondary	4876369	792093	16%
Feb-16	TUP	Secondary	1508351	105822	7%
Feb-16	TUT	Secondary	2479742	4060	0%
Feb-16	TVL	Tertiary	27575199	8373926	30%
Feb-16	UPET	Secondary	1238410	157640	13%
Total			165415376	44072234	19%

Whereas, it appears 21% in the visual, as shown below:

If the same values are plotted on "Clustered Column Chart", it apprears alright:

Where am I going wrong?

Detailed steps will go a long way in understanding my mistakes, and will be highly appreciated.

Regards,

Deepak

[v-huizhn-msft · Accepted solution]

Hi @deepvibha,

The measure and 100% Stacked Bar Chart is right. The 19% is incorrect. Because the 21% is percentage of Total Insurance Revenue for Total Revenue and Insurance Revenue in February 2016. While the 19% is the average of Insurance %, they are different, and stand for different meanings. Gross Insurance of Feb Stacked Bar Chart is come from 44072234/( 165415376+ 44072234)=21%. 44072234/( 165415376+ 44072234) is different from 19%+21%+30%+33%+22%+16%+7%+0%+30%+13%/12. For simple example, [3/(2+3)+5/(4+5)]/2 is not equal to (3+5)/(2+3+4+5).

If you have any question, please feel free to ask.

Best Regards,
Angelia

[deepvibha]

Thanks @v-huizhn-msft

Actually I had realized it later. I have now recalculated it accordingly and its appearing correct.

Thanks.

Regards,

Deepak