cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

## Wrong % on 100% Stacked Bar Chart??

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

1 ACCEPTED SOLUTION
Employee

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).

Best Regards,
Angelia

2 REPLIES 2
Employee

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).

Best Regards,
Angelia

Thanks @v-huizhn-msft

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

Thanks.

Regards,

Deepak

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors