How to use Tooltip to display breakdown data for a specified ranking

[v-xinruzhu-msft]

Scenario: 

In this post, tooltip and measure are utilized to show the breakdown of data for different classifications. 

  

Expected Result: 

Calculate the number of customers in each category, show the top five categories of customer number, merge other data into "others".  

And show the data details corresponding to the top five data through tooltip.  

If the data is merged into Others, the tooltip is blank. 

Sample Data: 

How: 

1.First create a Measure to calculate the number of Customers for each product 

count = CALCULATE(COUNT('Table'[Customer]), ALLEXCEPT('Table', 'Table'[Product])) 

2.This step is achieved by displaying the first five products of the number of customers based on the COUNT number, merging the other data into "Other". This is a Calculated column because we need to put it into a table and not be affected by the row context. 

Product1 =  
VAR _rank = RANKX(ALL('Table'), [count], , DESC, Dense) 
RETURN 
IF(_rank <= 5, [Product], "others") 

 

3.Here, we need to recalculate the number of Customers per Product based on the category names from the previous step. 

Customercount = CALCULATE(COUNT('Table'[Customer]), ALLEXCEPT('Table', 'Table'[Product1])) 

4.In this step I will explain how to create the tooltip. 

I created Page2 and then changed that Page to Tooltip in Format -> Canvas Setting -> Type 

Putting the breakdown data to be displayed into a table visualization. It's worth noting that you can achieve the desired effect in the Tooltip table without the Product field, but I chose to put in the Product field for readability. 

Putting the breakdown data to be displayed into a table visualization. It's worth noting that you can achieve the desired effect in the Tooltip table without the Product field, but I chose to put in the Product field for readability. 

Up to this point, the basic Tooltip is complete, but it is not possible to achieve the effect that Product is categorized as "others" and the Tooltip is empty. 

5.Next we'll create a Measure to implement the effect of the tooltip being empty when the product is categorized as "other". 

Measure =  
VAR _product = CALCULATE(MAX([Product]), FILTER('Table', [Product] = [Product1])) 
RETURN 
IF(MAX([Product1]) = "others", 0, 1) 

Put the measure into the visual-level filters, set up show items when the value is 1. 

Summary: 

This method can be used when you have a summary page and want to view specific details on the summary page. 

For more details about tooltips, you can read related document link: 

Create tooltips based on report pages 

Customize tooltips in Power BI 

Author: Xinyi X. 

Reviewer: Ula and Kerry 

How to use Tooltip to display breakdown data for a specified ranking.pbix