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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
rsbin
Super User
Super User

Customize Tooltip for Stacked Column Chart

Good Afternoon,

Have searched the Board for an answer, but to no avail.

I am deploying a 100% stacked bar chart wherein I am summing total seconds (in the hundreds of thousands).

Stacked Tooltip.png

I have created a new measure which converts seconds into hh:mm:ss format (Formatted Measure).

Other than creating a Custom Tooltip Page, is there a quick way to get the Tooltip to refer to this Formatted Measure instead of the Measure in total seconds?

If I drag the Formatted Measure into the Tooltip field well, it shows for all groupings in my stacked column.  I only want it to refer to the group selected.

If I have to create a custom tootip page then I can do that.

 

Thanks much and appreciate any suggestions!

 

 

15 REPLIES 15
Mayank_Yavda
Resolver II
Resolver II

Hi @rsbin,

 

Here is Stacked Column Chart with Custom Tooltip which will help you get separate or ungrouped tooltip for each stack.

 

 

Custom Tooltip Stacked Column Chart  PBIVizEdit.comCustom Tooltip Stacked Column Chart PBIVizEdit.comStacked Column Chart  with custom tooltip PBIVizEdit.comStacked Column Chart with custom tooltip PBIVizEdit.com 

Download link for the custom visual file in this page

https://pbivizedit.com/gallery/stacked-column-with-custom-tooltip 

 

This was made with our Custom Visual creator tool PBIVizEdit.com. With this tool,

  • anyone, irrespective of technical skills, can create their own visuals
  • 15 minutes to create a visual from scratch
  • opens up many additional attributes to edit (for e.g. labels, tooltips, legends position, etc)

Give this a shot and let us know if you face any problem/errors.
You can use the editor to modify your visual further (some modifications cannot be done in Power BI window and have to be in editor).

Thanks,
Team PBIVizEdit

 

smelcher
Frequent Visitor

How did you create the new measure to converts seconds into hh:mm:ss format (Formatted Measure)?

Good Morning @smelcher 

 

Found the following code in another thread.  Can't recall who provided it.  I have seen various ways to do this, but this one works for me:

 

EquipIDDuration_Format = 
VAR hours =
    ROUNDDOWN ( [EquipIDDuration] / 3600, 0 )
VAR minutes =
    ROUNDDOWN ( MOD ( [EquipIDDuration], 3600 ) / 60, 0 )
VAR seconds =
    INT ( MOD ( [EquipIDDuration], 60 ) )
RETURN
    FORMAT(hours,"00") & ":"
        & FORMAT(minutes, "00")
        & ":"
        & FORMAT(seconds, "00")

[EquipIDDuration] is the original measure calculated in seconds.

If you download the pbix attached in this thread, @v-zhenbw-msft has also provided something similar.

 

Good Luck and best regards,

I am still doing something wrong; am also a newbie.  I created a custom col as picture indicates, but it doesn't like the format.  Also, I really needs days too because 1 million seconds converts to 11 days 13:46:40 per Tools4Noobs.  Thanks, Sandra

 

 Custom Column.jpg

@smelcher,

Sorry, but I neglected to reply to your issue about "days."   Once you get the hh:mm:ss working, we can modify the DAX code to try to bring in the days format.

Now I need to adjust the DAX code to format the days... How do I do that? Below code converts 47564440 seconds into HH:MM:SS as 13212:20:40 

 

ViewDuration_Formatted =
INT ('Micro-Lesson Views'[ViewDuration] / 3600 ) & ":"
& RIGHT(
"0"
& INT(([ViewDuration] - INT ( [ViewDuration] / 3600 ) * 3600 ) / 60),2) & ":"
& RIGHT( "0" & MOD ( 'Micro-Lesson Views'[ViewDuration], 3600),2)

I was able to find code that worked to show the days and got it to work in my visual "card".  Now I need to figure out how to make it work in my visual "table".  Any ideas?

Table without Duration as a Column.jpg

Hello @smelcher,

 

I am not sure if your Total View Duration is a Measure or a Column.  If it is a Column just drag it into your Table.

However, since it is a Text field it will not have a Total.  

DateDuration (Sec)Duration (Days)
8/1/2019 0:004587500 days 12:44:35
8/2/2019 0:004678400 days 12:59:44
8/3/2019 0:001458300 days 04:03:03
8/4/2019 0:001035300 days 02:52:33
8/5/2019 0:004201200 days 11:40:12
8/6/2019 0:004324600 days 12:00:46
8/7/2019 0:004339500 days 12:03:15
8/8/2019 0:004703000 days 13:03:50
8/9/2019 0:004461700 days 12:23:37
 337895 

 

My suggestion would be to not display Totals in the Table, but use your Card Visual or other Visual to display your Totals.  Other Members of the Community might have some better ideas if you would start a new thread or search YouTube for videos.

That's the extent to which I can be of help.  Glad you were able to get the code working for you as it is different then what I am using.

 

Good Luck going forward

Hi again @smelcher ,

 

It looks like I mis-spoke in my earlier reply to you.   Based on the picture below, Power BI will total the Duration in Days column.

Format Days.png

This caught me by surprise as I didn't look closely enough.  Just thought I would let you know.

 

Best Regards,

Hello @smelcher ,

I too am pretty new at this stuff, but I think the issue is you are creating a custom column in M (Power Query).   The code for M would be much different.

The code I provided is DAX.  So in Power BI Desktop, select the Modeling menu from the ribbon and Select "New Column".

Good Luck and let me know how it goes.

 

Regards,

v-zhenbw-msft
Community Support
Community Support

Hi @rsbin ,

 

We understand your requirement, but unfortunately Power BI cannot replace EquipIDDuration with another measure called EquipID_Formatted.

Because the “hh:mm:ss” format is a text type and when you put a text type to Values, it just count it.

 

Cus 1.jpg

 

So we just can create a hh:mm:ss column and add it to tooltip.

 

hh:mm:ss = 
INT('Table'[time] / 3600) & ":" &
RIGHT("0" & INT(('Table'[time] - INT('Table'[time]/ 3600) * 3600) / 60), 2) & ":" &
RIGHT("0" & MOD('Table'[time], 3600), 2)

 

The result like this,

 

Cus 2.jpg

 

If you have any question, please kindly ask here and we will try to resolve it.

BTW, pbix as attached.

 

Best regards,

 

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Good Morning @v-zhenbw-msft 

 

Thank you for the response.  I examined your pbix file to see how you set this up.  I see you created a table so that the formatted value changes for each value in the column.   I am using Measures for all of my calculations. 

Measures.png

These Measures, when brought into the stacked column chart are filtered by several slicers on the page.  I can slice by Date, Facility, and other variables. 

I do not know how I would convert these Measures into a "table" as you have done and still keep all of the contextual filters.

If you have any further advice or suggestions, I would be most appreciative.

 

Again, thank you for taking the time to respond.

 

Kindest regards,

Hi @rsbin ,

 

You can replace the hh:mm:ss column with this measure and put the measure to Tooltips. But you cannot put this measure to Values, because it is text type.

 

EquipID_Formatted = 
INT ( [EquipIDDuration] / 3600 ) & ":"
    & RIGHT (
        "0"
            & INT ( ( [EquipIDDuration] - INT ( [EquipIDDuration] / 3600 ) * 3600 ) / 60 ),
        2
    ) & ":"
& RIGHT ( "0" & MOD ( [EquipIDDuration], 3600 ), 2 )

 

The result like this,

 

CUS1.jpg

 

If you have any question, please kindly ask here and we will try to resolve it.

BTW, pbix as attached.

 

Best regards,

 

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-zhenbw-msft
Community Support
Community Support

Hi @rsbin ,

 

Sorry for that we don’t understand your requirement clearly.

If you mean not to display the EquipIDDuration data, this does not seem to be possible. Because the fields in Values must be displayed in Tooltips. It is a default function.

 

Custom 1.jpg

 

The specific operation steps refer to this article.

If you have any question, please kindly ask here and we will try to resolve it.

 

Best regards,

 

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-zhenbw-msft 

The data in EquipIDDuration is in total seconds.

I need to use that field to properly display the stacked column chart.  However displaying seconds in the hundreds of thousands is meaningless to the User.

I would like to replace EquipIDDuration with another measure called EquipID_Formatted which is formatted in the equivalent of hh:mm:ss which will be much more meaningful.

My question is can this be done without creating an entirely new Tooltip Report Page.   I just want to change the display of that one value.

Appreciate your thoughts and suggestions.

 

Best Regards,

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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