cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Trend Line for Bar Chart Tracking 5% Reduction on Last Year's Totals

Hi All,

 

I need to add a trend line to a bar chart which shows the targeted 5% reduction against last year's totals. So if last year's total was 100 the trend line for this year needs to be at 95. 

 

The trend line also needs to filter by Type and Sub-Type 1 & Sub-Type 2  

 

How can I do this please?

 

Thanks 

2 REPLIES 2
Highlighted
Super User IV
Super User IV

@owdtelly , Try like , with time intelligence and date table

 

95% = CALCULATE(sum('order'[Qty]), previousyear('Date'[Date]))*.95

or

95% = CALCULATE(sum('order'[Qty]), previousyear('Date'[Date]),all('Order'))*.95

 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted

Thank you for your help, I have just updated my origianl post to mention some filtering I need. The trend line needs to be for a specfic type and sub-types. For example type is water and sub-types are potable mains and potable abstracted

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors