Stacked bar chart - Always show specific column

[QuangMC]

Hello,

I'm having a problem with a specific display that has been asked in a report.

Edit : Pbix example file : https://www.dropbox.com/s/ykr4a6yev07d2t8/TestByRegion.pbix?dl=0

Context

I have a single Fact table with results for multiple indicators and regions.

I also have 2 Dim tables containing information on indicators and regions.

It's a very simple model, that looks like this :

The task is to add a stacked bar chart to display the results for each region, and indicators.

First step is to create my measures to get the results for each indicators (since they each have different calculation methods, I have to create a few different measures).

For this example, let's say : 

Measure1= CALCULATE(AVERAGE(fact[results]), dim[indicator] = "Value1")

Measure2= CALCULATE(MEDIAN(fact[results]), dim[indicator] = "Value2")

So in Axis, I add the region field I need.

And in Value, I add every measure I created that I need to show. It looks like the following.

My users need to be able to compare themselves to the "National" results, for each indicators, which do not exist by default. So what I did was add a "NAT" value in my regions table (literally just adding a line in the Dim table). It is not linked to anything by default, but it allows me to show a "NAT" column in my visual.

Then, for the National results, I just add a condition in each measure that ignores the REGION column with an ALL function, and then use an IF to chose which result to show.

It now, all measures look like :

Measure1=
IF(
MAX(Dim[Region]) = "NAT"
,CALCULATE(AVERAGE(fact[results]), dim[indicator] = "Value1", ALL(Dim[Region]))

,CALCULATE(AVERAGE(fact[results]), dim[indicator] = "Value1")
)

It might not be optimal, but it works perfectly (we checked the real results, and they are correct).

The problem

Now the problem is when users want to filter on specific regions, or even when RLS is applied to specific users. 

When you filter on one region, you loose the NATIONAL results. This is logical, since when the region is filtered, my IF condition won't find "NAT", and the result won't show.

But I need to ALWAYS display the NAT results IN THE CHART (and as individual bar), regardless of the applied region filter. 

I have tried multiple approachs, like use a REMOVEFILTERS function in my measure, or even put up a second visual on top of the first one with only the national results, but every way i try either looks terrible, is not very practical, or just doesn't work.

Maybe this is something some of you have already experienced ? Have you found a solution or a workaround ? 

Or did i miss something obvious ? 

If you need more information, please do not hesitate to ask.

[Hei_aQing]

Hi @QuangMC 

Can you share a sample file, I would like to try it.

[QuangMC]

Hello @Hei_aQing ,

Sorry for the delay, i'm having trouble with sharing a PBIX file.

I think dropbox should work : https://www.dropbox.com/s/ykr4a6yev07d2t8/TestByRegion.pbix?dl=0

The file contains random numbers for the values (so the results can look weird, which is normal), but the core model is almost the same as the one I need to use.

Please tell me if you need any more information.