Forum Discussion
Trend Line in Power BI
Hello,
I want to have a trend line on the following visualization.
This graph is based on following formula.
TICKET COUNT TREND =
SUMMARIZE (
vRCCLCOMBINED,
vRCCLCOMBINED[WEEK START DATE AND END DATE],
vRCCLCOMBINED[WEEK NUMBER],
vRCCLCOMBINED[QUARTER],
"TICKET COUNT INCIDENTS", COUNTX (
FILTER ( vRCCLCOMBINED, vRCCLCOMBINED[DATA ORIGINATE] = 1 && vRCCLCOMBINED[START TIME].[Year] = MAX(vRCCLCOMBINED[START TIME].[Year])),
vRCCLCOMBINED[TICKET ID]
),
"TICKET COUNT WORKORDERS", COUNTX (
FILTER ( vRCCLCOMBINED, vRCCLCOMBINED[DATA ORIGINATE] = 2 ),
vRCCLCOMBINED[TICKET ID]
)
)
Any help with how can I do it will be appreciated.
Thanks
Abdulquadir
Just in case, I came across this thread which talks about creating trendlines with DAX. You could try writing your own measure and then decide whether you want to use the combined bar and line chart or the DIY method.
Regards,Paul.
11 Replies
Not sure if it will work for you, but you could try creating a seperate trend line visual and stack it on top of the original, with the formatting adjustments to ensure it is clear. I tried this out:
The main setback for this solution is that the tooltips in the stacked chart won't be accesible since the visual lies behind the trend line visual, though you could include a complemetary Pie Chart to show the values selected. (And Focus mode will only bring up the selected visual of course).
Edit: IF you do go down this route, make sure to play around with the "Edit Interactions" to ensure that the overlying trend line doesn't become confusing. For example, in the example with the Pie Chart, to make sure the markers line up with the corresponding month, I activated the option "Show Items with no data" in the month field of the trend line chart. By this, if I select a portion within the pie chart to filter the other visuals, the markers remain aligned with their corresponding months.
Basically it needs tweaking a bit, but is doable.
I simple created a Line Chart in a separate visual, made the backround transparent, moved the Y Axis to the right, turned off the x axis and then overlayed it over the stacked bar visual. (in the image I posted, you can see both individual visuals (Line Chart and stacked chart on the top centre and right. The final visual on the top left is the Stacked chart with the Line chart on top of it, adjusting the size of the frame so that the individual markers of the Line chart match the bars of the Stacked Chart underneath.
For illustration purposes of what I am trying to explain, here are both visuals. I've added a greyish backround to the Line chart so you can see the actual visual lying on top of the Stacked Chart. (You obviously need to set this Line Chart backround to transparent by truning off the backround in the Line Chart visual in the actual report page.
Also make sure you bring the Line Chart visual to the front
Hi ashaikh,
Unfortunately, Trend line is not supported for Stacked Column chart.
Regards,
Yuliana Gu
I am aware that Trend Line is not available in stacked column. But is there any other way to do. May be manually.
Also I tried on Clustered column chart with X-axis being categorical still it does not work.
I am aware that Trend Line is not available in stacked column. But is there any other way to do. May be manually.
Also I tried on Clustered column chart with X-axis being categorical still it does not work.
