Measures: Absolute variation and Relative contribution

[Gaul10]

Good morning everyone,

i'm trying to create two new measures that allow me to identify how much every value has increased during the years.

Here an example of my table:

Year     Access     Application

2016    100          Mobile

2016    200          Desktop

2016    300          Tablet

2017    500          Mobile

2017    800          Desktop

2017    600          Tablet

2018    800          Mobile

2018    900          Desktop

2018    600          Tablet

So, the total accesses of each year would be:

2016 = 600

2017 = 1900

2018 = 2300

The measures i'm trying to crate must tell me the percentage increase/decrease of each application between the years in two way:

1) Absolute variation

2) Relative contribution

For example, taking Desktop as a reference:

2016 = 200

2017 = 800

2018 = 900

The absolute variation would be:

2016 --> 0%

2017 --> [(DesktopAccess2017/DesktopAccess2016)*100 ] -100 = [(800/200)*100] -100 = 300%

2018 --> 12.5%

The relative contribution would be:

2016 --> (DesktopAccess2016/TotalAccess2016)*100 --> (200/600)*100 = 33.3% 

2017 --> 42.1%

2018 --> 39.1%

How can i create these two measures?

Thank you for your help

[alexei7 · Accepted solution]

Hi Gaul10,

I think the following measures would work for you:

Absolute Variation = var CurrentYear = MAX(Table1[Year]) return
DIVIDE(sum(Table1[Access]),CALCULATE(sum(Table1[Access]),FILTER(ALLEXCEPT(Table1,Table1[Application]),Table1[Year]=CurrentYear-1)),0)-1

Relative Contribution = DIVIDE(SUM(Table1[Access]),CALCULATE(SUM(Table1[Access]),ALLEXCEPT(Table1,Table1[Year])),0)

[Gaul10]

It works perfectly! 

Thank you alexei7