Tooltip Displays All Fields Instead of Selected Field in 100% Stacked Column Chart

[manoj_0911]

Tooltip Displays All Fields Instead of Selected Field in 100% Stacked Column Chart

Hi everyone,

I’m working with a 100% stacked column chart in Power BI where each column represents various statuses (e.g., Available, Away, Break, etc.) in seconds. I’ve created individual measures to convert these seconds into `HH:MM:SS` format for each status.

Here’s what I want to achieve:
- When I hover over a specific section of the chart (e.g., Available), the tooltip should only show the `HH:MM:SS` format for that specific status, not for all statuses.

Steps I’ve Taken:
1. Created individual measures for each status in `HH:MM:SS` format.
2. Added these measures to the tooltip field well of the chart.
3. Created a dynamic tooltip measure to display the relevant formatted time based on the hovered status.

Current Issue:
Even though I have configured the dynamic measure, all statuses are showing up in the tooltip, and I only want the tooltip to display the relevant status when hovering.

**Here’s the Dynamic Tooltip Measure I’m Using:**

``

Dynamic_Tooltip =

VAR __category =

    SWITCH(

        TRUE(),

        ISFILTERED('SAMPLE'[AVAILABLE]), "Available",

        ISFILTERED('SAMPLE'[AWAY]), "Away",

        ISFILTERED('SAMPLE'[BREAK]), "Break",

        ISFILTERED('SAMPLE'[BUSY]), "Busy",

        ISFILTERED('SAMPLE'[COMMUNICATING]), "Communicating",

        ISFILTERED('SAMPLE'[IDLE]), "Idle",

        ISFILTERED('SAMPLE'[INTERACTING]), "Interacting",

        ISFILTERED('SAMPLE'[MEAL]), "Meal",

        ISFILTERED('SAMPLE'[MEETING]), "Meeting",

        ISFILTERED('SAMPLE'[NOT_RESPONDING]), "Not Responding",

        ISFILTERED('SAMPLE'[OFF_QUEUE]), "Off Queue",

        ISFILTERED('SAMPLE'[OFFLINE]), "Offline",

        ISFILTERED('SAMPLE'[ON_QUEUE]), "On Queue",

        ISFILTERED('SAMPLE'[SYSTEM AWAY]), "System Away",

        ISFILTERED('SAMPLE'[TRAINING]), "Training",

        BLANK()

    )

RETURN

    SWITCH(

        __category,

        "Available", [SUM AVAILABLE DD:HH:MM:SS],

        "Away", [SUM AWAY DD:HH:MM:SS],

        "Break", [SUM BREAK DD:HH:MM:SS],

        "Busy", [SUM BUSY DD:HH:MM:SS],

        "Communicating", [SUM COMMUNICATING DD:HH:MM:SS],

        "Idle", [SUM IDLE DD:HH:MM:SS],

        "Interacting", [SUM INTERACTING DD:HH:MM:SS],

        "Meal", [SUM MEAL DD:HH:MM:SS],

        "Meeting", [SUM MEETING DD:HH:MM:SS],

        "Not Responding", [SUM NOT RESPONDING DD:HH:MM:SS],

        "Off Queue", [SUM OFF_QUEUE DD:HH:MM:SS],

        "Offline", [SUM OFFLINE DD:HH:MM:SS],

        "On Queue", [SUM ON_QUEUE DD:HH:MM:SS],

        "System Away", [SUM SYSTEM AWAY DD:HH:MM:SS],

        "Training", [SUM TRAINING DD:HH:MM:SS],

        BLANK()

    )

```

**Questions:**
- How can I ensure that only the relevant measure is displayed in the tooltip based on the hovered field?
- Are there any additional steps or configurations I might be missing to achieve this behavior?

Any help or suggestions would be greatly appreciated!

[manoj_0911]

- **Tooltip Value is Constant**: The tooltip displays the same value regardless of which segment (status) or month I hover over. This suggests that the tooltip is not dynamically updating based on the selected segment.
- **Parameter and Seconds Values**: I need the tooltip to include both the parameter value and the formatted seconds value.

### Exact Requirements:
For each segment (status) within a month, the tooltip should display:
- **Month**: For example, "September 2024"
- **Status**: For example, "Offline"
- **Total Seconds**: For example, "158068469" (42.25%) (This is a raw number representing seconds)
- **Formatted Time**: For example, "0:00:00:00" (Formatted as DD:HH:MM:SS)

[v-rzhou-msft]

Hi @manoj_0911 ,

As far as I know, it is hard for your data model to achieve this goal.

I think it will be easier if you can unpivot columns (from [AVAILABLE] ... to [OFFLINE]) in Power Query Editor.

You can also remove value = 0 by filter to reduce data size.

let
    Source = ...,
    Sheet38_Sheet = Source{[Item="Sheet38",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet38_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"START_TIME", type datetime}, {"OFF_QUEUE", Int64.Type}, {"AVAILABLE", Int64.Type}, {"BUSY", Int64.Type}, {"AWAY", Int64.Type}, {"BREAK", Int64.Type}, {"MEAL", Int64.Type}, {"SYSTEM AWAY", Int64.Type}, {"MEETING", Int64.Type}, {"TRAINING", Int64.Type}, {"ON_QUEUE", Int64.Type}, {"IDLE", Int64.Type}, {"NOT_RESPONDING", Int64.Type}, {"INTERACTING", Int64.Type}, {"COMMUNICATING", Int64.Type}, {"OFFLINE", Int64.Type}, {"TEAM", type any}, {"Trend Date", type date}, {"Trend Hour", type datetime}, {"Trend Week", type text}, {"Trend Month", type date}, {"TrendMonthNumber", type any}, {"SortMonthNumber", type any}, {"Trend Month Sort", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"START_TIME", "TEAM", "Trend Date", "Trend Hour", "Trend Week", "Trend Month", "TrendMonthNumber", "SortMonthNumber", "Trend Month Sort"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Columns", each ([Value] = 900)),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Attribute", "Legend"}})
in
    #"Renamed Columns"

Measure will be easy.

Dynamic ToolTips = 
VAR __total_seconds = SUM('Sample'[Value])
VAR __days = INT(__total_seconds / 86400)  // Calculate days
VAR __hours = INT(MOD(__total_seconds, 86400) / 3600)  // Calculate remaining hours
VAR __minutes = INT(MOD(__total_seconds, 3600) / 60)  // Calculate remaining minutes
VAR __seconds = MOD(__total_seconds, 60)  // Calculate remaining seconds
VAR __result = FORMAT(__days, "0") & ":" & FORMAT(__hours, "00") & ":" & FORMAT(__minutes, "00") & ":" & FORMAT(__seconds, "00")
RETURN
IF(ISBLANK(__total_seconds), BLANK(), __result)

Result is as below.

Best Regards,
Rico Zhou

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

[manoj_0911]

Thank you so much for your response could you please attach the pbix file

[v-rzhou-msft · Accepted solution]

Hi @manoj_0911 ,

Here is my sample file, I hope it could help you solve your issue.

Best Regards,
Rico Zhou

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

[manoj_0911]

Thanks a lot

[Jonvoge]

Hi manoj_0911

Have you tried creating a Custom Page, building a simple Card with your dynamic tooltip measure, and tried to use the Custom Page as the tooltip instead? I have a feeling that might solve your problem, without being totally sure.

_____________________________________________________
I hope my comment was helpful.
If your question was answered, please mark your post as 'Solved' and consider giving me a 'Thumbs Up'.
Find me on LinkedIn, Sessionize, or my blog Downhill Data

[manoj_0911]

Thanks for your suggestion , let me try that