The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
Hello everyone,
I wanted to check if anyone has found a workaround for setting up a custom calculated reference line in Power BI.
While the default reference line options are useful and make sense in many scenarios, I would like to use a custom DAX measure instead. Unfortunately, Power BI currently doesn’t seem to support this directly.
To clarify what I’m aiming for:
I’d like to display a custom calculated line for each column in a column chart. If that’s not possible for multiple columns, is there at least a way to achieve this for a visual with a single column?
I’ve included an example below to illustrate the goal more clearly.
Currently I am using a column insted of the line but doesn't look that great.
Thanks in advance for any ideas or suggestions!
Best,
Marius
Hello everyone,
Thank you for your prompt reply on this.
I am not looking for the combo chart option as doesn't meet the visual requirements. The goal is to apply a individual line calculated on a custom measure for each of the columns. That is why I am using another column at the moment in lack of something similar.
I will share a pbi example file once I put one together which will hopefully clarify the intent.
Thank you,
Marius
Hi @mdm2025,
I would also take a moment to thank @jaineshp , @wardy912 for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.
Best Regards,
Harshitha.
Hi @mdm2025,
I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We are always here to support you.
Regards,
Harshitha.
Hi @mdm2025 can you please share pbix file so that I can understand what's going on
Hi ,
To create per-category reference lines, use a Line and clustered column chart plus a custom DAX measure. First, define a measure that returns the desired “target” for each category (e.g. day of week) using SWITCH and SELECTEDVALUE. For example, if your axis is the day of the week, you could write:
Custom Reference Line =
SWITCH(
SELECTEDVALUE('Date'[Day Name]),
"Monday", 1500,
"Tuesday", 1300,
"Wednesday", 500,
"Thursday", 1200,
"Friday", 900,
"Saturday", 800,
"Sunday", 700,
BLANK()
)
Add the measure to the combo chart: In a Line and clustered column chart, put your primary measure in the Column values and the new Custom Reference Line measure in the Line valuescommunity.fabric.microsoft.com. Power BI will draw a horizontal line at the specified value for each...
Optional – use tooltips: You can also include the custom measure in the tooltip to display exact values on hover.
The result looks like this:
Figure: A line-and-column chart where each day’s column is shown in blue and the custom reference line (target) in greencommunity.fabric.microsoft.comcommunity.fabric.microsoft.com.
And with tooltips enabled, hovering over a column shows both the column value and the target. For example, hovering on Monday displays a tooltip with Order Quantity = 1884 and Custom Reference Line = 1500:
Figure: Tooltip for Monday showing Order Quantity (1884) and the Custom Reference Line value (1500)community.fabric.microsoft.com.
Steps summary:
Create a DAX measure using SWITCH(SELECTEDVALUE(...)) to output the target for each categorycommunity.fabric.microsoft.comcommunity.fabric.microsoft.com.
Use a Line and clustered column chart, placing the main measure in the column values and the custom measure in the line valuescommunity.fabric.microsoft.com.
(Optional) Add the measure to tooltips for clarity.
This approach fully achieves per-column reference lines using DAX in Power BI.
Hi @mdm2025
You will need to create a measure similar to the below, I am setting different targets for different days of the week.
Custom Reference Line =
SWITCH(
SELECTEDVALUE('Date'[Day Name]),
"Monday", 1500,
"Tuesday", 1300,
"Wednesday", 500,
"Thursday", 1200,
"Friday", 900,
"Saturday", 800,
"Sunday", 700,
BLANK()
)
Add that as the line y-axis in a line and clustered column chart for the following result
You could also add it as a tooltip for better visibility
I hope this helps! Please give a thumbs up and mark as solved if it does, thanks!
User | Count |
---|---|
80 | |
78 | |
37 | |
34 | |
31 |
User | Count |
---|---|
93 | |
81 | |
60 | |
49 | |
49 |