cancel
Showing results for 
Search instead for 
Did you mean: 
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
Microsoft
Microsoft

@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

 

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.

Patt
Frequent Visitor

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
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

Check it Out!

Mark your calendars and join us on Thursday, September 29 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors