Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ashaikh
Helper III
Helper III

Trend Line in Power BI

Hello,

 

I want to have a trend line on the following visualization.

trend.png

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

1 ACCEPTED SOLUTION

@ashaikh

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.





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

11 REPLIES 11
PaulDBrown
Community Champion
Community Champion

@ashaikh

 

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:

 

 

 

stack&trend.JPG

 

  

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.

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






@PaulDBrown

 

How did you make trend line visual?

@ashaikh

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.

Trend line overlay.JPG2018-04-19_LI.jpg

 Also make sure you bring the Line Chart visual to the front

 

 

 

 

 

 

 

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






I understand that. But what does your line chart shows in this case.

@ashaikh 

 

It's just a sales trend line. It's a dummy model I use to test measures and try out stuff. the stacked chart are sales; the trend line chart are sales.

 

The data itself is irrelevant; I was just trying to help you by offering a DIY solution. Not perfect by any stretch of the imagination, but doable.

 

Edit: just out of curiosity, what trend line are you trying to include?





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






I want to show trend line based on the ticket count weekly. As shown in my initial question.

@ashaikh

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.





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






@ashaikhSo can't you create a separate Line Chart with the same measure and overlay it?





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






v-yulgu-msft
Employee
Employee

Hi @ashaikh,

 

Unfortunately, Trend line is not supported for Stacked Column chart.

 

Regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.