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
owdtelly
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
amitchandak
Super User
Super User

@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.

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
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.