Steps to Create Dynamic Tooltips for Different Columns:
Create Measures for Each Tooltip Calculation: You need to create individual measures for each of the tooltip values (e.g., percentage variance for "Month1 vs Month2", "Month2 vs Month3", etc.). These measures will calculate the value dynamically based on the column you are hovering over.
For example, let's create measures for the percentage variance between months:
Month 1 vs 2 Diff:
dax
Copy code
Month1_vs_2_Diff =
IF(
NOT(ISBLANK([Month 1])) && NOT(ISBLANK([Month 2])),
([Month 2] - [Month 1]) / [Month 1],
BLANK()
)
Month 2 vs 3 Diff:
dax
Copy code
Month2_vs_3_Diff =
IF(
NOT(ISBLANK([Month 2])) && NOT(ISBLANK([Month 3])),
([Month 3] - [Month 2]) / [Month 2],
BLANK()
)
You will have a similar measure for each pair of columns where you want to display a specific tooltip value.
Set Up Dynamic Tooltip Measure: Create a dynamic measure that will display the appropriate value based on the column being hovered over. Power BI has the ability to show different values in the tooltip when hovering over different fields, but you'll need to use conditional logic based on the column.
Here is an example dynamic measure that will change based on the column:
dax
Copy code
DynamicTooltipMeasure =
SWITCH(
TRUE(),
HASONEVALUE('Table'[Month 1 vs 2 diff]), [Month1_vs_2_Diff],
HASONEVALUE('Table'[Month 2 vs 3 diff]), [Month2_vs_3_Diff],
BLANK()
)
This measure checks which column is being hovered over (based on HASONEVALUE checking the context of that column) and returns the corresponding value (the percentage variance).
Use the Dynamic Tooltip Measure in Your Tooltip:
In the Visualizations pane, under Tooltip, drag the DynamicTooltipMeasure into the Tooltips field well.
This measure will now dynamically display the value depending on which column you hover over.
Test the Tooltip:
When you hover over a specific column (e.g., Month 1 vs 2 diff or Month 2 vs 3 diff), the tooltip will display the corresponding percentage variance calculation based on your dynamic measure.
Additional Tips:
Tooltips for Each Column: You could also create specific Tooltips pages for different columns if you want a more customized and detailed tooltip that can include multiple fields or more complex information.
Formatting: Make sure your measures return formatted values for percentages (e.g., 0.00% format) so that the tooltip is readable.
Example Use Case in a Table:
Let’s say you have a table with the following columns:
Month1 Month2 Month3 Month1 vs 2 diff Month2 vs 3 diff
100 80 120 -40% 20%
200 250 200 -50% 50%
When you hover over the "Month1 vs 2 diff" column in the table, the tooltip will show the calculated percentage variance for Month 1 vs Month 2. When you hover over the "Month2 vs 3 diff" column, it will show the calculated percentage variance for Month 2 vs Month 3.
Conclusion:
