Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi, i have a line chart with a reference line below. How can i display a message whenever my data point exceeds the reference line?
Solved! Go to Solution.
Hi @weijunawj
In addition to amitchandak's reply, I will show the way in details.
Firstly, I need to know how did you get the reference line. Is it an average line in visual analysis?
If you want to show different colors to distinguish whether the value exceeds the average, build a measure as below.
Color =
VAR _AVERAGE = CALCULATE(AVERAGE('Table'[Value]),ALL('Table'))
RETURN
IF(SUM('Table'[Value])>_AVERAGE,"RED","BLUE")
Build a bar chart and use FX in Data color in Format.
Select Field value in Format by and based on color measure. Then select ok and transform the bar chart to line chart.
Result is as below.
If you need to a tooltip to show whether the value exceeds the average, you can change the result in color measure and add this measure into tooltips in this visual.
Tooltips =
VAR _AVERAGE = CALCULATE(AVERAGE('Table'[Value]),ALL('Table'))
RETURN
IF(SUM('Table'[Value])>_AVERAGE,"Above Avg","Below Avg")
To see the tooltip, you need to hang your mouse on the point in line chart. Or the tooltips will be hidden.
You can build a tooltip based on report page as well.
For reference: Create tooltips based on report pages in Power BI Desktop
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @weijunawj
In addition to amitchandak's reply, I will show the way in details.
Firstly, I need to know how did you get the reference line. Is it an average line in visual analysis?
If you want to show different colors to distinguish whether the value exceeds the average, build a measure as below.
Color =
VAR _AVERAGE = CALCULATE(AVERAGE('Table'[Value]),ALL('Table'))
RETURN
IF(SUM('Table'[Value])>_AVERAGE,"RED","BLUE")
Build a bar chart and use FX in Data color in Format.
Select Field value in Format by and based on color measure. Then select ok and transform the bar chart to line chart.
Result is as below.
If you need to a tooltip to show whether the value exceeds the average, you can change the result in color measure and add this measure into tooltips in this visual.
Tooltips =
VAR _AVERAGE = CALCULATE(AVERAGE('Table'[Value]),ALL('Table'))
RETURN
IF(SUM('Table'[Value])>_AVERAGE,"Above Avg","Below Avg")
To see the tooltip, you need to hang your mouse on the point in line chart. Or the tooltips will be hidden.
You can build a tooltip based on report page as well.
For reference: Create tooltips based on report pages in Power BI Desktop
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@weijunawj , One way is to create conditional formatting on bar visual and convert back to the line. But if the other line is not avg line and is measure it might not work
Color =
if( [Value] > [Avg Value], "Green",
"Blue"
)
Use in conditional formatting with a field value option
or create a measure and use on tool tip
Color =
if( [Value] > [Avg Value], "Above Avg",
"Below Avg"
)
Hi sir, sorry i'm new to power bi. How can i create a measure and use on tool tip as mentioned in your reply? and where do i enter the condition "
Color =
if( [Value] > [Avg Value], "Above Avg",
"Below Avg"
)" ?
Thank you
@weijunawj , Assume you have a measure value, then you can create a new meausre
Color =
var = calculate([Value], allselected(Table))
return
if( [Value] > [Avg Value], "Above Avg", "Below Avg" )
Then add that to tooltip (Visual Pane)
User | Count |
---|---|
94 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |