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
Seanan
Solution Supplier
Solution Supplier

Grand total percentage for clustered bar chart with 2 x-axis

Hi all,

I have a clustered bar chart that look like this:

PBIDesktop_MRaiTKhGqp.png

 

 

 

 

The red bar is "All Learners" the blue bar is "Filtered Learners". On screen there are a few slicers that filter down the selected learners bar. The code for both measures is as follows:

 

 

All Learners = CALCULATE(COUNT('LA_LSSurvey'[Keycode]),ALL(LA_LSLearners))
Selected Learners = COUNT(LA_LSSurvey[Keycode])

 

 

There are 2 tables, LA_LSLearners and LA_LSSurvey. The survey table contains all survey responses and the learners table contains all details about a learner.

 

What I now need is for the x-axis to be scale locked to 100% (0-100%). Each all learners bar for each response (agree, disgaree, etc.) will then have a percent against a possible 100%. For example there are 8 learners in total, 4 said agree, 2 disagree and 2 strongly disagree. That would mean that the agree bar for all learners will be locked at 50% and the other 2 at 25%. Then when a user selected filters from the slicers they can narrow down the selected learners bar percentage without affecting the all learners bar.

 

I tried to make my measures "show value as: GT% of Total", and this almost gave me exactly what I was looking for. The only issue I had was that the all learners bar worked fine but when I then tried to filter down the selected learners bar it would go above the all learners bar instead of filtering down based on the all learners bar percent. See example below:

PBIDesktop_MhDE9M6Mg0.pngPBIDesktop_rlJcxJWNVP.png

 

 

 

 

Where as it should have become a smaller bar than the red bar.

1 ACCEPTED SOLUTION
Seanan
Solution Supplier
Solution Supplier

I managed to resolve this issue in SQL, by working out the percentage value of each row based on the 2 conditions using a CASE statement. From there I was able to work out the total sum of each answer for each question as a percentage and this allowed for the selected learners percentage to filter down based on the all learners percentage.

PBIDesktop_Ct1Nrv4NU8.png

View solution in original post

1 REPLY 1
Seanan
Solution Supplier
Solution Supplier

I managed to resolve this issue in SQL, by working out the percentage value of each row based on the 2 conditions using a CASE statement. From there I was able to work out the total sum of each answer for each question as a percentage and this allowed for the selected learners percentage to filter down based on the all learners percentage.

PBIDesktop_Ct1Nrv4NU8.png

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.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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