Dynamic formatting not working with stacked bar chart total labels

[AhmadBakr]

I am using a stacked bar chart displaying a amounts on the x-axis. as the amounts range is huge, I used for the measure formatting a dynamic format to display amounts in Billions/Millions/Kilos or simple decimal numbers.

Data labels use the same measure as the bars, and formatting shows as expected. However, Total labels does not reflect the dynamic formatting as it should. Display units value formatting for both the Data label and Totals label are set to none already.

Screen shots below. I noticed a similar Issue raised in 2023 and 2024 already! Links also below.

Dynamic measure formatting (preview) not displayin... - Microsoft Fabric Community
Dynamic formatting issue with total label in bar c... - Microsoft Fabric Community

[danextian]

Hi @AhmadBakr 

While you can use a different measure as a data lable for the individual stacks, the total label uses the actual measure in the bars. Using a different one on the total isn't currently supported. 

[AhmadBakr]

Hi @danextian, exactly.

In fact I tried it, and what u mentioned was the case.

Interesting enough, I went to do some experiments with the sample data table I shared, I discovered that I the data of the first series (the firs legend point) is in the K order, and the total is in the M order, the problem happens! As if the totals are calculating the format from the dynamic format of the first series!

This is not final yet, but I noticed it while exploring this issue.

[danextian]

Did you perhaps change the display units of the total labels to thousands?

[AhmadBakr]

No.

Anyways, my observation is confirmed. Whenever the first series in a category is in a thousands order less than the totals, the dynamic formatting of its thousands order will be used for the totals!!!

Checkout the below dataset. Whenever you move the value 111000 to be in series1 or any category, the problem will appear in this category total!

Interesting enough, if you put it in series1, but sort the legend descendingly, the problem disappears!!! The low value has to be the closest to the category axis to get the problem showing! screenshots for this data set below.

Category		Series		Value		Series Order
A		Series1		36455255.53		1
A		Series2		254872457.9267		2
A		Series3		73592647.062		3
B		Series1		54324777.36		1
B		Series2		73592647.062		2
B		Series3		42313511.578		3
C		Series1		76457777.36		1
C		Series2		53923471.74		2
C		Series3		48923471.74		3
D		Series1		111000		1
D		Series2		26555355.53		2
D		Series3		44444444.865		3

small value 111000 in series1 cat-D, legend sorted ascending --> problem

now, legend sorted descending... no problem with cat-D

[AhmadBakr]

Below is fictitious data in a table to illustrate the case.

Use it in a clustered chart (column or bar):

• Put Cat on the category axis: Y-axis if bar chart, X-axis if column chart
• Series in the legend
• Value in the value axis: X-axis if bar chart, Y-axis if column chart

use the following measure:

sumtest = SUM(Formatting[Value])

Format this measure as dynamic, and use the below for the dynamic formatting code:

VAR val = SELECTEDMEASURE()

VAR k = 10^3    VAR m = 10^6    VAR b = 10^9

RETURN

    SWITCH(TRUE(),

        val >= b, "#,##0,,,.00B",

        val >= m, "#,##0,,.00M",

        val >= k, "#,##0,.00K",

        "##0.00"

    )

Show Data Labels and Total Labels

Format Display units for values of Data Labels as "None"

Format Display units for values of Total Labels as "None"

Table Name: Formatting
Cat    Series    Value

A	X	1234567
B	X	5234567
C	X	2345635
D	X	123463
A	Y	2087654
B	Y	2654321
C	Y	467375
D	Y	3219768
A	Z	7098765
B	Z	3456721
C	Z	2222467
D	Z	1114674

The below is the result!

  

[kushanNa]

Hi @AhmadBakr 

I couldn’t dig deep into the issue you're facing, but a quick workaround I can suggest for the stacked column chart is to use a Line and Stacked Column Chart and set the line label as the total.

Once you create the line, make its color 100% transparent.

For the data labels, set the display units of all series to None, but keep it as Auto only for the specific measure.

[AhmadBakr]

Hello @kushanNa. Thank you for your response.

Yes, I thought of the same, however unfortunately I am using a stacked bar chart in my report, hence the fix I am in! I included in my demo a stacked column chart in addition to the bar chart to show the problem is with the stacked charts in general, as it appeared to me.

[kushanNa · Accepted solution]

Oh, I see. How about trying to overlay two stacked bar charts as a temporary solution?

Create two stacked bar charts:

• One with the legend

• One without the legend

Enable totals in both charts and position the totals at the outer edge.

In the chart with the legend, make the total value's color transparent using the following code.

Transparent = "#FFFFFF00"

At the bottom chart , you can make the bar colors transparent 100% .

The end result should look like this:

[AhmadBakr]

Hi, @kushanNa 

I followed your advise. It worked. I have to say though that it consumed a lottttt of effort and measures creation and testing, in order to ensure that user interaction will not break the setup. My dashboard contains a lot of field parameters and slicers.

Below is a screenshot of my actual dashboard after I applied the layering, vs. before with error formats marked. Just to share with you the joy.

Thank you for the idea. I was hesitant to do it, but when you suggested it, I decided to give it a shot.

This definitely did not solve the "bug" per se, MS have to do their part, but it did solve my problem as a work around. Thanks a lot.

[AhmadBakr]

Nice workaround. Let me check how it will work with user interaction on the graph, as it is needed.