Variance % between Current Year(2020) and Previous Years (2019, 2018, 2017, 2016)

[Anonymous]

Hello Everyone,

I have a below dataset

I have created a line and clustered column chart. Pumpage_MGD is represented in columns for years in the below line and clustered column chart.

I would like to calculate variance % for 2020-2019, 2020-2018, 2020-2017, 2020-2016 and display Variance % as line in Line and Clustered column chart.

I have a slicer for years. When I select 2020 and 2019 in slicer, I should display 2020-2019 Variance % as line in Line and Clustered column chart. When I select 2020 and 2018 in slicer, I should display 2020-2018 Variance % as line in Line and Clustered column chart and so on.

I would like to show the chart as shown below

Please let me know how to achieve this.

Thank you!

Regards,

Aswini C S.

[Icey · Accepted solution]

Hello @Aswini_C_S ,

Try this:

Variance % =
VAR Top1N =
    VALUE (
        CALCULATE (
            CONCATENATEX ( TOPN ( 1, VALUES ( 'Table'[Year] ) ), 'Table'[Year], ", " )
        )
    )
VAR Top2N =
    VALUE (
        CALCULATE (
            CONCATENATEX (
                TOPN ( 1, VALUES ( 'Table'[Year] ), 'Table'[Year], DESC ),
                'Table'[Year],
                ", "
            )
        )
    )
VAR FirstSelectedValue =
    CALCULATE ( SUM ( 'Table'[PUMPAGE_MGD] ), 'Table'[Year] = Top1N )
VAR SecondSelectedValue =
    CALCULATE ( SUM ( 'Table'[PUMPAGE_MGD] ), 'Table'[Year] = Top2N )
RETURN
    DIVIDE ( SecondSelectedValue - FirstSelectedValue, FirstSelectedValue )

This way, you can choose any two years for comparison. Or compare the older and smaller years in the selected years.

Reference: DAX – Calculating the difference between 2 selections in a slicer.

Best regards

Icey

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

[Anonymous]

Hi @Icey ,

I need to calcualte average of 2018, 2019 PUMPAGE_MGD and need to display as another column in line and clustered column chart.

For Example- Average(2018-03-W1 Pumpage_MGD, 2019-03-W1 Pumpage_MGD), Average=e(2018-03-W2 Pumpage_MGD, 2019-03-W2 Pumpage_MGD), Average(2018-03-W3 Pumpage_MGD, 2019-03-W3 Pumpage_MGD) and so on..

I also need to calcualte Variance % between 2018-2019 Average and 2020 data.

Please find the below chart for your reference.

I need to add 2018-2019 avg in "year" slicer and If I select 2018-2019 Avg and 2020 in slicer, the chart should display as shown in  above screenshot.

Please let me know how to solve this.

Thank you!

Regards,

Aswini

[Anonymous]

Hi @Icey 

Thank you for your reply and solution.

If I don't select any year in slicer, I shouldn't see Variance%. If I select more than 2 years in slicer, I shouldn't see Variance%. I should be able to see Variance % only when I select any of the 2 years in slicer.

Is there any way to do this. Please let me know.

Thank you!

Regards,

Aswini C S.

[Anonymous]

I just changed a bit in the above DAX and It worked. 

Variance % =

VAR YearCount= CALCULATE(COUNTROWS(FILTERS('Table'[Year])),ALLSELECTED('Table'[Year]))

VAR Top1N =

VALUE (

CALCULATE (

CONCATENATEX ( TOPN ( 1, VALUES ( 'Table'[Year] ) ), 'Table'[Year], ", " )

)

)

VAR Top2N =

VALUE (

CALCULATE (

CONCATENATEX (

TOPN ( 1, VALUES ( 'Table'[Year] ), 'Table'[Year], DESC ),

'Table'[Year],

", "

)

)

)

VAR FirstSelectedValue =

CALCULATE ( SUM ( 'Table'[PUMPAGE_MGD] ), 'Table'[Year] = Top1N )

VAR SecondSelectedValue =

CALCULATE ( SUM ( 'Table'[PUMPAGE_MGD] ), 'Table'[Year] = Top2N )

RETURN IF(YearCount=2,DIVIDE(SecondSelectedValue - FirstSelectedValue, FirstSelectedValue ))

Thank you!