Forum Discussion
Helper Vdisplay values and calculations in tooltip
I would appreciate your support in displaying values and calculations in the tooltip. I would like the following to happen when the mouse hovers over the graph: 1. The value selected by the mouse is displayed. 2. The first value of my selected period (the oldest value) is displayed. 3. The percentage difference calculation is displayed. This way, when the mouse moves, the calculation is recalculated using the newly selected value against the oldest value.
7 Replies
Hans-Georg_PulsSuper User
Hi telesforo1969 ,
first step is to generate a new page as tooltip page:
- New page
- Go to new page
- If you want to change the canvas settings "Type" to "Tooltip" to define a size that fits for most tooltips
- Go page to your page with the line chart
- Go to Properties of the line chart and change Tooltips->Options to "Report page" and <name of your new page"
- Check if the empty tooltip pops up, if you hoover over your line chart
- Go back to your tooltip page
- Keep in mind, that the filter context of this page is defined by the position of your mouse.
- Design and define the content of your tooltip
- A and B are the minimum value of the columns A and B.
- For the earliest value you need two measure like this one for A and B:A At Earliest Date =VAR _MinDate = CALCULATE(Min(DemoLineWithTooltip[Date]), REMOVEFILTERS(DemoLineWithTooltip[Date]))RETURNCalculate(Min(DemoLineWithTooltip[A]),REMOVEFILTERS(DemoLineWithTooltip),DemoLineWithTooltip[Date] = _MinDate)
- For the difference you need two measures like this one for A and B:A Diff =VAR _EarliestValue = [A At Earliest Date]VAR _CurrentValue = Min(DemoLineWithTooltip[A])RETURNDIVIDE(_CurrentValue - _EarliestValue, _EarliestValue)
The result would look similar to this one:
Does that help you? And is it that what you wanted? Do you need further assitance?
telesforo1969That's what I need, I'm going to implement it and I'll let you know the result.
- telesforo1969
I forgot to mention two important things.
1. I have many series, but the example only showed two. I need it to be dynamic.
2. I need to change the date filter and recalculate the minimum value.
As you can see in the image, I've already calculated the values, but when I place these measures in the tooltip, it displays the wrong value. `REMOVEFILTERS` for the date doesn't work when I change the date period; it always shows the last value regardless of the selected period.
I think that if I can have a calculated measure in the tooltip, I can add other measures to calculate the percentage, for example.
Note: I can create and configure the tooltip itself; I just need the measurement that displays the first value of the selected period when I hover the mouse over the chart (tooltip).
- Hans-Georg_Puls
Hi telesforo1969 ,
I hope that I have a solution for both of your points.
To make the date range dynamic you should do the following:
- Create a table with all distinct date values from your data table
- Something like the following in DAX: DateRangeDates = SELECTCOLUMNS(YourDataTable, "RangeDate", YourDataTable[Date])
- Or something like the following in Power Query:
let
Source = Table.SelectColumns(YourDataTable, "Date"),
#"Removed Duplicates" = Table.Distinct(Source)
in
#"Removed Duplicates" - Create a relationship between the date columns of the date table and your data table
- Assign the date column of the newly created date table to the slicer for the date range
- The new date table is used nowhere else!
To make the solution work with a dynamic number of series I had to adjust the table structure (maybe you already have this structure). The should look similar to the following table:
To get the tooltip you want you need the following 3 measures: (the names of your columns and tables might be different)
Earliest Date =
VAR _MinDate = CALCULATE(Min(DemoLineWithTooltipDynamic[Date]), REMOVEFILTERS(DemoLineWithTooltipDynamic[Date]))
RETURN
_MinDateValue at Earliest Date =
VAR _MinDate = CALCULATE(Min(DemoLineWithTooltipDynamic[Date]), REMOVEFILTERS(DemoLineWithTooltipDynamic[Date]))
RETURN
Calculate
(
Min(DemoLineWithTooltipDynamic[Value]),
REMOVEFILTERS(DemoLineWithTooltipDynamic[Date]),
DemoLineWithTooltipDynamic[Date] = _MinDate
)Diff To Earliest =
VAR _EarliestValue = [Value at Earliest Date]
VAR _CurrentValue = Min(DemoLineWithTooltipDynamic[Value])
RETURN
DIVIDE(_CurrentValue - _EarliestValue, _EarliestValue)You should format the second measure as decimal and the last as Percentage.
I close with some important steps concerning the configuration of your visuals (the names of your columns might be different)
- Line Chart
- Assign "Value" to the Y-axis of the line chart. You have to choose summarization here. Min or Max are fine, Count not
- Assign "Series" to Legend
- Tooltip Page (based on a Card (new) visual)
- Value = Min of "Value"
- Categories = "Series"
- Reference Labels
- Earliest Date measure
- Value At Earliest Day measure
- Diff To Earliest measure
You will run into trouble if your list of series gets to long. As far as I know it is not possible to scroll inside a tooltip.
Hope this approach works for you as well.
- Create a table with all distinct date values from your data table
- Ashish_Mathur
Hi,
Try these measures in a tooltip
First = calculate([Total],datesbetween(calendar[date],minx(all(calendar),calendar[date]),minx(all(calendar),calendar[date])))
Diff = [Total]-[First]
Hope this helps.
