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
ChrisPBI
Advocate II
Advocate II

Display Percent Changes alongside a Line Chart

Hello everyone,

 

is it possible to display percent changes alongside a line chart?

 

That is my use case:

 

1.png

 

 

 

 

 

 

 

 

 

 

 

 

I would like to display this in the graph in between the sum values.

 

E.g. from 2009 with 2618 to 2010 with 6127.

 

This functionality would be sufficent for consecutive years .

 

Thanks and Regards,

Chris

 

1 ACCEPTED SOLUTION

@ChrisPBI

 

  1. Create a calendar table.
    calendar TABLE = CALENDAR("2001-01-01","2016-12-31")
  2. Map one:many relationship.
    Capture.PNG
  3. Change your measure to
    LastYearAmount = CALCULATE(SUM('GGE Amounts'[GGE in kg CO2e]),PREVIOUSYEAR('calendar TABLE'[Date]))
  4. Done
    Capture.PNG

 

Check the details in the attached pbix, the unzip password is sent via private message.

 

 

View solution in original post

10 REPLIES 10
Eric_Zhang
Employee
Employee

@ChrisPBI

 

A line and column combo chart would be a better visual to show. In this case, the key is the PREVIOUSYEAR function. Check more details in the attached pbix.

 

Percent Change =
VAR LastYearAmt =
    CALCULATE ( SUM ( Table1[GGE in kg CO2e] ), PREVIOUSYEAR ( Table1[Date] ) )
RETURN
    IF (
        ISBLANK ( LastYearAmt ),
        0,
        ( SUM ( Table1[GGE in kg CO2e] ) - LastYearAmt )
            / LastYearAmt
    )

 

Capture.PNG

 

Anonymous
Not applicable

Hi!

I'm having the same problem as in this post. I checked your PBIX file and the solution seems to do what it should but in your example, you have only one entry per year:

KarenCuellar_0-1598551171607.png

In my case, I have several entries per month in each year. So, what I get is this:

KarenCuellar_1-1598551257498.png

Is showing me the trend along each month from (sept) 2013 to (July) 2020. How can I make it appear only per the year 2013 to 2020?

 

I hope you could answer this post as I know is pretty old. 

Thanks in advance!

Hi Eric,

 

that is a very cool possibility.

 

I can't get it work with my setting.

 

You have my .pbix, could you maybe try it?

 

I tried: LastYearAmount = CALCULATE(SUM('GGE Amounts'[GGE in kg CO2e]);PREVIOUSYEAR('Models'[Release Date]))

 

I don't get an error with that, but it's not given an result.

 

I would expect to get this table (from your example) but with my dates:

 

1.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

Best would be to only have the years in the first row.

 

Therefore, I tried it with my year extraction: YEAR('Models'[Release Date]), but that's not working at all.

 

Thanks and Regards,

Chris

@ChrisPBI

 

  1. Create a calendar table.
    calendar TABLE = CALENDAR("2001-01-01","2016-12-31")
  2. Map one:many relationship.
    Capture.PNG
  3. Change your measure to
    LastYearAmount = CALCULATE(SUM('GGE Amounts'[GGE in kg CO2e]),PREVIOUSYEAR('calendar TABLE'[Date]))
  4. Done
    Capture.PNG

 

Check the details in the attached pbix, the unzip password is sent via private message.

 

 

Hi Eric,

 

thank you very much, that works!

 

But it is not possible without the calender table, right?

 

Why can't I use my year column for that?

 

Regards,

Chris

@ChrisPBI

 

The function PREVIOUSYEAR/MONTH and some other timeintelligence functions should be on contiguous, nonrepeating dates.

completed line chartcompleted line chartDatasetDatasetI tried to use the example above to get percent change to calculate for my data and couldn't get it to work for the life of me.

 

 

Hi @Patt,

 

Show a dataset, explain the question and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@Ashish_Mathur

 

The data set shows staff levels (each liene and average salary (y-axis) from 2010 to 2017 (x-axis). I would like to show the year on year percent change for salary over the 2010 to 2017 period on the graph. Screenshot of the data and graph are attached. The data used for the line chart are the engineer types, engineer salary and date (dd/mm/yyy). 

 

completed line chartcompleted line chartDatasetDataset

Hi,

 

Share the link from where i can download your file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.