P L E A S E  W A I T...

Power BI: Dynamic Tooltip for the Funnel View

Clients approach Nabler to make their Microsoft Power BI dynamic tooltips more readable and user-friendly. As you can see in the image below, when a user hovers on a particular metric, the tooltip displays all of the data and definitions in their marketing funnel. To fix this so the tooltip only displays a chosen metric, you can deploy a dynamic dimension technique using a switch statement and variables in DAX.

In the instance below, the expanding window displays all metrics, which could confuse the end user. In this example, the user is hovering on Leads but all the metrics are being defined and displayed.

A bit code in DAX will make this menu dynamic, and to show only the specific definition and logics for a chosen metric. Let’s explain this step by step.

Imagine the Funnel View has following measures:

  • Total Leads: Total number of leads generated.
  • Sales accepted leads: Total number of leads accepted by sales.
  • Total Opportunities: Leads converted to opportunity.
  • Closed/Won opportunities: Total number of opportunities that has been closed/won.

It would be easier to read and use if the menu displayed only the metric chosen, as shown below:

To do this, follow these steps to:

Step 1: Create a new table with “Tooltip Selection” as the name of the table. Enter the metric names as the values of the table. Load the table.

Step 2: Create a new measurement for the Funnel View. Here, the measurement is called Value.

Funnel View Measure= VAR Selection =
SELECTEDVALUE ( ‘Tooltip for Funnel'[Tooltip Selection], “All” )
RETURN
SWITCH (
TRUE (),
Selection = “Leads”,[Leads],
Selection = “Total Opportunities”,[Funnel Tab1_opportunities],
Selection = “Closed/won”,[Funnel Tab1_closed/won1],
Selection = “Sales Accepted Leads”,[Sales Accepted Leads])

Now bring in the Tooltip Selection column from the tooltip for funnel table that we created to group field in funnel visual and funnel view measure.

Step 3: Create a page for the tooltip.

Add a new page and name it as “Tooltip for funnel.” Under the paint brush icon (format), change the page information as follows:

Page name: Tooltip for funnel
Toggle the tooltip and enable it
Change page size to: Type-Tooltip

Now, let’s create a measurement to make the Tooltip dynamic and name it Description Tooltip.

Description Tooltip = VAR Selection =
SELECTEDVALUE ( ‘Tooltip for Funnel'[Tooltip Selection], “All” )
RETURN
SWITCH (
TRUE (),
Selection = “Leads”, “Leads “& [Leads]& UNICHAR(10) &”Dictionary: Description – Count of distinct lead ids based on date range and filter criteria selected.”,
Selection = “Total Opportunities”,”Total Opportunities ” &[Funnel Tab1_opportunities]
& UNICHAR(10) & ” ” &”Percentage of Total Opportunities by SAL – “&FORMAT(DIVIDE([Funnel Tab1_opportunities],[Sales Accepted Leads]),”0.00%”)
& UNICHAR(10) & ” ” &”Percentage of Total Opportunities by Leads – “&FORMAT(DIVIDE([Funnel Tab1_opportunities],[Leads]),”0.00%”)
& UNICHAR(10) &”Logic – Count of distinct Opportunity Ids from the leads that are selected based on date range and filter criteria (#1 above). Opportunities not connected to a lead will be excluded.”,
Selection = “Closed/won”,”Closed/Won Opportunities ” &[Funnel Tab1_closed/won1]
& UNICHAR(10) & ” ” &”Percentage of Closed/Won by Total Opportunities – “&FORMAT(DIVIDE([Funnel Tab1_closed/won1],[Funnel Tab1_opportunities]),”0.00%”)
& UNICHAR(10) & ” ” &”Percentage of Closed/Won by Leads – “&FORMAT(DIVIDE([Funnel Tab1_closed/won1],[Leads]),”0.00%”)
& UNICHAR(10) &”Logic – Count of Opportunities that are in Closed/Won Status. “,
Selection = “Sales Accepted Leads”,” Sales Accepted Leads ” &[Sales Accepted Leads]
& UNICHAR(10) & ” ” &”Percentage of Sales Accepted Leads by Leads – “&FORMAT(DIVIDE([Sales Accepted Leads],[Leads]),”0.00%”)
& UNICHAR(10) &”Logic – Leads that are not in qualifying, rejected or nurture status.”)

Bring in a card visual and fit it so it covers the whole page. Then, add the Description Measure to the field value of the card.

The value shows as “(Blank)” because we have not selected any values, yet.

Step 4: Go back to the Funnel Visual and, under paint brush icon (Format), enable the tooltip.

  • Type: Report page
  • Page: Tooltip for funnel

Now, when users hover on a particular measurement they will only see its value and definition!

Drive better results by understanding customer data