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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
QuangMC
Frequent Visitor

Stacked bar chart - Always show specific column

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 :

QuangMC_1-1655892857854.png

 

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.

QuangMC_2-1655892981272.png

 

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

QuangMC_3-1655893058994.png

 

 

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.

2 REPLIES 2
Hei_aQing
Helper I
Helper I

Hi @QuangMC 

 

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

 

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.

 

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.