Forum Discussion

TimNYC's avatar
TimNYC
Helper I
6 years ago
Solved

Percent Change Between Values on Line Chart

I've been asked to show % change between years on the following chart. At present there are two lables per X-axis value because there are two years included in my legend. Is it possible to have one lable per X-axis value, and that lable be the % change from 2019 to 2020? Or is there a better way to show this data?

 

 

Thank you,

 

Tim

Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    6 years ago

    hi  TimNYC 

    I think the best way is add it into Tooltip of line chart.

    and there is another workaround for you refer to:

    Step1:

    Use combo chart instead of line chart in the report.

    https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-combo-chart

    Step2:

    Create different measure by year, eg.

    2019 = CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Year]=2019))

2020 = CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Year]=2020))

    Step3:

    Drag these measures into line values.

    Step4:

    Create a Percent Change measure like below:

    Difference = 
var lastyear=  CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER ( ALL ( 'Table'[Year]), 'Table'[Year] = MAX ( 'Table'[Year] ) - 1 )
    )
return

DIVIDE(CALCULATE(SUM('Table'[Value]))-lastyear,lastyear,BLANK())

    Then drag this measure into column values

    Step5:

    You could change the color of the column to white and custom the two Y-axis in format.

    Result:

     

    and here is sample pbix file, please try it.

     

    Regards,

    Lin

2 Replies

  • v-lili6-msft's avatar
    v-lili6-msft
    Community Support
    6 years ago

    hi  TimNYC 

    I think the best way is add it into Tooltip of line chart.

    and there is another workaround for you refer to:

    Step1:

    Use combo chart instead of line chart in the report.

    https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-combo-chart

    Step2:

    Create different measure by year, eg.

    2019 = CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Year]=2019))

2020 = CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Year]=2020))

    Step3:

    Drag these measures into line values.

    Step4:

    Create a Percent Change measure like below:

    Difference = 
var lastyear=  CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER ( ALL ( 'Table'[Year]), 'Table'[Year] = MAX ( 'Table'[Year] ) - 1 )
    )
return

DIVIDE(CALCULATE(SUM('Table'[Value]))-lastyear,lastyear,BLANK())

    Then drag this measure into column values

    Step5:

    You could change the color of the column to white and custom the two Y-axis in format.

    Result:

     

    and here is sample pbix file, please try it.

     

    Regards,

    Lin