cancel
Showing results 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

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:

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:

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

1 ACCEPTED SOLUTION
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.

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.

Announcements

#### 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 Monthly Update - June 2024

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

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

#### New forum boards available in Real-Time Intelligence.

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

Top Solution Authors
Top Kudoed Authors