Stacked bar chart doesnt's show values based on filter.

[piotr_gor]

Hi, 

I try to create stacked bar chart with a legend with two different values - one of them is green, second - red. I want to keep sum of those two at the chart. I don't want to see red and green values separately on chart - always a total.  

Problem I stumbled is if I filter the data to show only red values my label doesn't show up. In case of filtering the data I want to display label of only selected filter. 

I created three measures: CALCULATE sum of reds, CALCULATE sum of greens and then SUM of both of them. I thought that might be the answer but it isn't. 

What should I do to achieve desired chart?

[v-jayw-msft]

Hi @piotr_gor ,

You could create a measure like below:

Measure =
CALCULATE ( SUM ( 'table'[value] ), ALLEXCEPT ( 'table', 'table'[Product] ) )

Best Regards,

Jay

[MFelix]

Hi @piotr_gor 

If you want to always get the values of the red and green your metric need to use a ALL statment to pass over the filter of the red / green.

[DataOnIce]

Hi, @MFelix. I think I might be trying to do the same thing as @piotr_gor.

I have a stacked bar chart where the Product is on the Y-axis and the Color is the Legend. I also have a slicer to choose either Green, Red, or both. If I select Green, I want the data label to reflect the value for Green. If I select Red, I want the data label to reflect the value for Red. If I select both, I want the data label to reflect the total for both Red and Green without showing the individual data label for both. I just want the total. Currently, having the Total labels switched on gives the desired result only if I've selected both Red and Green. If I only select one color, no data label is displayed.

The image above is my desired outcome.

Thankful for any help you're able to provide.

[MFelix]

Hi @DataOnIce ,

What happens is that when you have a single value there is no total lables because it's a single value.

In this case you need to create the following measures:

Total Quantity = SUM('Table'[Quantity])

Color Coding = 
var ColorQuantity = CALCULATE([Total Quantity], ALLSELECTED('Table'[Color]))
var TotalQuantity = CALCULATE([Total Quantity], ALL('Table'[Color]))
Return
IF(ColorQuantity = TotalQuantity,SELECTEDVALUE('Table'[Color]) ,"Black")

Now use your color coding as a conditional formatting on the data labels:

[DataOnIce]

Thank you @MFelix 

Ended up using the code below:

Color Coding =
VAR RightsSelection =
SELECTEDVALUE ( SlicerRights[Rights] )
RETURN
SWITCH (
TRUE (),
AND ( [GBO] = [GBO_RightsNo], ISBLANK ( RightsSelection ) ), "#118DFF",
AND ( [GBO] = [GBO_RightsYes], ISBLANK ( RightsSelection ) ), "#12239E"
)

Please let me know if you think it can be made more efficient.

[MFelix]

Hi @DataOnIce ,

Look good.

[DataOnIce]

Thank you @MFelix 

I think I kind of understand. Are you able to help modify the measure so that the legend is actually between Yes and No instead of Red and Green where the color code for Yes is #12239E and the color code for No is #118DFF?

Will the one below work or is there a more efficient approach?

Color Coding =
VAR SelectedRights = SELECTEDVALUE(ReleasesDetails[Rights])
RETURN
IF (
SelectedRights = "No",
"#118DFF",
IF (
SelectedRights = "Yes",
"#12239E",))