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
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
Sept PBI Carousel

Power BI Monthly Update - September 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.