% difference between years for average sales

[mfarali]

Hi,

I have a situation where I have sales data from across the years. I would like to show on average what is the % change from one year to the other based on slicer selection. Something like the table low. 

I would like to show the % difference between lets say 2018 to 2021 in a line graph based on slicer selection. I have tried to use DAX measures but I cant seem to get it work.

Can anyone please help me on this?

Thanks in advance

[mfarali]

not really, the difference could be any year vs any year (as chosen in the slicer)

So the viewer could select 2019 and 2021. or 2018 and 2019. or any other combo that they want to see the % difference for.

[FreemanZ]

so in any selection, the difference is a single value, why being plotted with year on the x axis?

[mfarali]

the difference is a single value if only 2 years are selected. but if the viewer wants to see the trend then they can select more than 2 years to see the changes in %

like choosing to see 2018, 2019, 2020 if they wanted to

[v-xiaosun-msft]

Hi @mfarali 

According to your description, I made a sample and here is my solution. Please follow these steps.

Sample data:

Create five columns to calculate totals.

Totals = SUM('Tabelle1'[values])

sum of 2018 = SUMX(FILTER('Tabelle1','Tabelle1'[Year]=2018),'Tabelle1'[values])

sum of 2019 = SUMX(FILTER('Tabelle1','Tabelle1'[Year]=2019),'Tabelle1'[values])

sum of 2020 = SUMX(FILTER('Tabelle1','Tabelle1'[Year]=2020),'Tabelle1'[values])

sum of 2021 = SUMX(FILTER('Tabelle1','Tabelle1'[Year]=2021),'Tabelle1'[values])

Create a calculated column to return the % of each year.

percentage = 
IF('Tabelle1'[Year]=2018,'Tabelle1'[sum of 2018]/'Tabelle1'[Totals],
IF('Tabelle1'[Year]=2019,'Tabelle1'[sum of 2019]/'Tabelle1'[Totals],
IF('Tabelle1'[Year]=2020,'Tabelle1'[sum of 2020]/'Tabelle1'[Totals],
IF('Tabelle1'[Year]=2021,'Tabelle1'[sum of 2021]/'Tabelle1'[Totals]))))

Then create a column to return the changes.

change =
IF (
    'Tabelle1'[Year] = 2018,
    0,
    'Tabelle1'[percentage]
        - CALCULATE (
            MAX ( 'Tabelle1'[percentage] ),
            FILTER ( 'Tabelle1', 'Tabelle1'[percentage] < EARLIER ( Tabelle1[percentage] ) )
        )
)

Finally, put "Year" and "change" into the line chart, you will get the result as below.

I attach my sample below for your reference.

Best Regards,
Community Support Team _ xiaosun

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mfarali]

Hi,

I tried it out but I think the calculations are off. Sharing a sample here

the % change is shown as 4.74% between 2018 to 2019 when its a lot more than that. the % increase would be (16,643,485 - 1,687,711)/1,687,711 = 886.15% increase

not sure how i can attach the pbix file here.

thanks

[mfarali]

Thanks  will try it out

[FreemanZ]

what will you put on the x-axis of the line graph?

can i say 2018 is the minimum and 2021 is the maximum of the slicer selection?

[mfarali]

The years will be on the x axis. so depending on selection, it could be 2018 to 2021, or 2019 to 2021 etc

[FreemanZ]

the difference is 2018 vs 2021 or 2018 vs 2019, 2019 vs 2020, 2020 vs 2021?