Forum Discussion

mfarali's avatar
mfarali
Frequent Visitor
3 years ago

% difference between years for average sales

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...
mfarali's avatar
mfarali
Frequent Visitor
3 years ago

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's avatar
    FreemanZ
    Icon for Super User rankSuper User
    3 years ago

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

    • mfarali's avatar
      mfarali
      Frequent Visitor
      3 years ago

      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's avatar
        v-xiaosun-msft
        Icon for Community Support rankCommunity Support
        3 years ago

        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.