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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.