Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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).
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!
Hi @rsbin,
Here is Stacked Column Chart with Custom Tooltip which will help you get separate or ungrouped tooltip for each stack.
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,
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
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
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
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?
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.
Date | Duration (Sec) | Duration (Days) |
8/1/2019 0:00 | 45875 | 00 days 12:44:35 |
8/2/2019 0:00 | 46784 | 00 days 12:59:44 |
8/3/2019 0:00 | 14583 | 00 days 04:03:03 |
8/4/2019 0:00 | 10353 | 00 days 02:52:33 |
8/5/2019 0:00 | 42012 | 00 days 11:40:12 |
8/6/2019 0:00 | 43246 | 00 days 12:00:46 |
8/7/2019 0:00 | 43395 | 00 days 12:03:15 |
8/8/2019 0:00 | 47030 | 00 days 13:03:50 |
8/9/2019 0:00 | 44617 | 00 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.
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,
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.
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,
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.
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,
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.
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.
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.
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,
User | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
73 | |
65 |