Reference line value in tooltip
Reference line is very useful when you need to compare, but sometimes more reference line will make chart too crowded. In this situation, you may need the value of reference line show in tooltip. Let's see how to show the right value.
We use Sample - Superstore datasource. First we create 3 simple calculated field:
Original Ref Line Value: SUM([Sales])
Table Ref Line Value and Pane Ref Line Value: get sum of value in partition and divide by partition rows number. These 2 calculated field has same formula, but they use different way to calculate.
Then drag Sales to Columns, Region and Category to Rows, 3 new fileds to detail as below:
Then we right click axis and add 2 reference lines as below. First is Entire Table as Scope, second is Per Pane. Other options are same: SUM(Sales) as Value, Average as aggregation Value as Label.
Then you will see below chart:
Just as I said, too many reference lines look too crowded. I want only show average value for each region in tool tip, then remove 4 region reference line.
If you look at tooltip now, it not what I want.
Table Ref Line Value along Table (Down) shows the right value. But Original Ref Line Value only show same number as Sales and Pane Ref Line Value along Table (Down) shows the whole table average value. Then we need change Pane Ref Line Value by using Pane (down).
Original Ref Line Value is not useful so we can delete that. The the tooltip get right number.