Forum Discussion
Measures: Absolute variation and Relative contribution
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
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)-1Relative Contribution = DIVIDE(SUM(Table1[Access]),CALCULATE(SUM(Table1[Access]),ALLEXCEPT(Table1,Table1[Year])),0)
2 Replies
- alexei7Continued Contributor
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)-1Relative Contribution = DIVIDE(SUM(Table1[Access]),CALCULATE(SUM(Table1[Access]),ALLEXCEPT(Table1,Table1[Year])),0)
- Gaul10New Member
It works perfectly!
Thank you alexei7