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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
DL123
Frequent Visitor

Calculate a percentage (of column total) in a bar chart based on 3 different columns.

Hi, I am trying to create a bar graph to show the percentage of “yes” answers based on the column totals.

 

I have been able to unpivot the 3 columns below ( “Sad”, “Happy”, “Angry”  )  to show the count of “yes” answers for each column. What I would like to display the “yes” answers as a percentage of the column total (the original columns before they were unpivoted) where a team is selected.

 

So for the data below:

sad percentage =  Responsible team is Team D (selected) ,  count of “yes”   (4)   /count of team (5) = 80%.

 

 

 ID NumberResponsible TeamSadHappy
8279Team DYesYes
14742Team DYesYes
15306Team D Yes
47219Team DYes 
77967Team DYesYes

 

I have been able to create a measure for this (shown in the card visualisation), but can't work out how to create this in a graph. At the moment the bar graph only shows the count.

 

 

image.png

 

 

 

 

 

 

 

Link to Excel spreadsheet and Pbix file below:

 

https://drive.google.com/drive/folders/1fGJC19XRKF5pbcjsjU8Hsx2A-qiER8-k?usp=sharing

 

 

Thanks, 

1 ACCEPTED SOLUTION
v-jiascu-msft
Employee
Employee

Hi @DL123,

 

Try this formula instead.

Sad percentage 2 =
CALCULATE ( COUNTA ( 'Sheet1'[Responsible Team] ), 'Sheet1'[Answer] = "Yes" )
    / CALCULATE ( COUNTA ( 'Sheet1'[Responsible Team] ), ALL ( 'Sheet1'[Answer] ) )

Calculate-a-percentage-of-column-total-in-a-bar-chart-based-on-3-different-columns

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-jiascu-msft
Employee
Employee

Hi @DL123,

 

Try this formula instead.

Sad percentage 2 =
CALCULATE ( COUNTA ( 'Sheet1'[Responsible Team] ), 'Sheet1'[Answer] = "Yes" )
    / CALCULATE ( COUNTA ( 'Sheet1'[Responsible Team] ), ALL ( 'Sheet1'[Answer] ) )

Calculate-a-percentage-of-column-total-in-a-bar-chart-based-on-3-different-columns

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

That works- thank you! @v-jiascu-msft

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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