Clustered bar chart with one static bar and one responsive bar

[Seanan]

Hi all,

I have a clustered bar chart that looks like this:

I also have some on-screen slicers that filter down characteristics such as: Gender, Ethnicity, etc.

There are 2 tables that I am getting the data from, LearnerTable and SurveyTable, which have a relationship using the Keycode column that is in both tables.

In the example image above, I have a simple measure that counts the survey keycodes. However, for the all learners bar in the chart I need this to be static so that it will not be affected when selecting the on-screen filters.

Would anyone be able to help me with this?

[amitchandak]

@Seanan , You can have measure like

Calculate([Your Measure], filter(all(Table], Table[learners] = Max(Table[learners] ) ) )

Assumes the axis column is learners

[Seanan]

Hi @amitchandak 

Thanks for your swift response.

So I've tried your measure with both tables:

All Learners = CALCULATE('MeasuresTable'[Selected Learners], FILTER(ALL('LA_LSLearners'),'LA_LSLearners'[Keycode] = MAX('LA_LSLearners'[Keycode])))

All Learners = CALCULATE('MeasuresTable'[Selected Learners], FILTER(ALL('LA_LSSurvey'),'LA_LSSurvey'[Keycode] = MAX('LA_LSSurvey'[Keycode])))

The first measure is returning no results and the second measure is only returning 1 result (No filters have been selected).

My axis are as follows:

 

An example of the outcome I'm looking for is:

All learners bar = 21 for Happy, 2 for Unhappy

Selected learners bar (with filters applied) = 12 for Happy, 1 for Unhappy. 

I hope this helps clear anything up.

[Seanan · Accepted solution]

I managed to find a solution using the following code:

All Learners = CALCULATE(COUNT('LA_LSSurvey'[Keycode]),ALLEXCEPT(LA_LSSurvey,LA_LSSurvey[SurveyAnswerEnglish]))

Thanks for trying to help @amitchandak