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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
erwinsc
Frequent Visitor

Strange behaviour in stacked column chart

Hi all,

 

I would like to present you with the following case. I have a simple table in Power BI Desktop, with three columns (Scope, Main and Value) as shown below.

erwinsc_0-1661438433909.png

I have also created a measure called “Duration” with the following syntax.

erwinsc_1-1661438462843.png

The above table and measure enable me to create the following matrix on the reporting canvas.

erwinsc_2-1661438637783.png

The first requirement is the show Duration in different formats. Not only do I want to show Duration in days (default format in this example) but also in hours, minutes and seconds. To do this I have created a calculation group called “Duration Formatter”.

erwinsc_3-1661438680891.png

The calculation group enables me to move between different formats without having to create additional measures. If you want to know how to do this, please follow this link to the blog of Sergio Murru. After insering the calculation group into the data model the matrix looks like this.

erwinsc_4-1661438727816.png

The second requirement is to show Duration in one additional format, called “DDDHHMM”. This format is not native to Power BI but Sergio Murru describes very well how this can be implemented. I’ve altered his example a little bit to accommodate my specific requirements but the method is basically the same.

erwinsc_5-1661438755392.png

erwinsc_6-1661438767564.png

erwinsc_7-1661438788166.png

After adding the new calculation item to the calculation table the matrix looks like this.

erwinsc_8-1661438815016.png

Now you may get a bit confused when you look at the above DAX logic that calculates the DDDHHMM value. Don’t worry, I’ve recreated all calculations in an Excel file, as shown below. Let me just say that the logic calculates a value that, in itself is nonsense, but which “fits” neatly within the “000:00:00” format.

erwinsc_9-1661438866733.png

OK, so far so good. We now have a table, a measure and a calculation group that is able to format the value that the measure produces in the formats that I want to have. Now the fun starts.

 

When I add a stacked column chart to the reporting canvas (Duration by Scope) I can also make use of the different formats that I have created in the calculation group.

 

Seconds

erwinsc_10-1661438925474.png

Hours

erwinsc_11-1661438941147.png

DDDHHMM

erwinsc_12-1661438957492.png

But when I add the Main column as a legend in the stacked column chart something strange happens when I use the DDDHHMM format.

 

Seconds (ALL GOOD)

erwinsc_13-1661439001188.png

Hours (STILL GOOD)

erwinsc_14-1661439022118.png

DDHHMM (WRONG)

erwinsc_15-1661439043580.png

As you can see the chart labels show the wrong format (I expect to see DDDHHMM format). But also, the height of the columns, in relation to each other, has changed from other formats. When I look back at the Excel file I can clearly see the values that Power BI uses in the Scope and Main scenario with the stacked column chart. This behaviour clearly differs from the behaviour in the matrix.

erwinsc_16-1661439087271.png

My conclusion is that the behaviour of the matrix visual and the stacked column chart visual is different in the Scope and Main scenario. The stacked column chart clearly shows the wrong value AND the wrong format.

 

My question is: Is there any way around this issue and, if so, how should I go about his? Or should I report this to Microsoft as a bug?

 

Many thanks in advance for your replies.

Greetings,

Erwin

1 REPLY 1
lbendlin
Super User
Super User

If this is important to you please consider voting for an existing idea or raising a new one at https://ideas.powerbi.com

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 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.

October NL Carousel

Fabric Community Update - October 2024

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