Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
deepvibha
Advocate II
Advocate II

Wrong % on 100% Stacked Bar Chart??

Hi,

 

I have a base table in Excel like below:

MonthHospitalHospTypeTotal RevenueInsurance Revenue
Feb-16CBETertiary323053626058119
Feb-16DGLSecondary2620280554956
Feb-16MDUTertiary6059793918332756
Feb-16PONDYTertiary243912237975557
Feb-16SALEMTertiary78225011717305
Feb-16THENISecondary4876369792093
Feb-16TUPSecondary1508351105822
Feb-16TUTSecondary24797424060
Feb-16TVLTertiary275751998373926
Feb-16UPETSecondary1238410157640
Mar-16CBETertiary3913237610090395
Mar-16DGLSecondary2799581556781
Mar-16MDUTertiary7356558020315306
Mar-16PONDYTertiary267799869598620
Mar-16SALEMTertiary89711411936270
Mar-16THENISecondary56759461139493
Mar-16TUPSecondary4261907159590
Mar-16TUTSecondary24672883960
Mar-16TVLTertiary355797009264903
Mar-16UPETSecondary2163418326724

 

which is imported in PBI like below:

 

Untitled.png

 

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:

 

Untitled.png

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

 

MonthHospitalHospTypeTotal RevenueInsurance RevenueInsurance %
Feb-16CBETertiary32305362605811919%
Feb-16DGLSecondary262028055495621%
Feb-16MDUTertiary605979391833275630%
Feb-16PONDYTertiary24391223797555733%
Feb-16SALEMTertiary7822501171730522%
Feb-16THENISecondary487636979209316%
Feb-16TUPSecondary15083511058227%
Feb-16TUTSecondary247974240600%
Feb-16TVLTertiary27575199837392630%
Feb-16UPETSecondary123841015764013%
Total  1654153764407223419%

 

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

Untitled.png

 

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

 

Untitled.png

 

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
v-huizhn-msft
Employee
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).

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


Best Regards,
Angelia

View solution in original post

2 REPLIES 2
v-huizhn-msft
Employee
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).

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


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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors