Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
I have a population projection dataset which I receive from a govnt agency. Sample below;
Update Year | Region | DHB_name | Sex | Ethnicity | ethnicity2 | Age_Group | Period | Period Type | Population | Period Index |
2017 | Other | Auckland | Female | Asian | Other | 00-04 | 2013 | Annual | 3700 | 1 |
2017 | Other | Auckland | Female | Asian | Other | 00-04 | 2014 | Annual | 3630 | 2 |
2017 | Other | Auckland | Female | Asian | Other | 00-04 | 2015 | Annual | 3770 | 3 |
2017 | Other | Auckland | Female | Asian | Other | 00-04 | 2016 | Annual | 3990 | 4 |
2017 | Other | Auckland | Female | Asian | Other | 00-04 | 2017 | Annual | 4290 | 5 |
2017 | Other | Auckland | Female | Asian | Other | 00-04 | 2018 | Annual | 4570 | 6 |
2017 | Other | Auckland | Female | Asian | Other | 00-04 | 2019 | Annual | 5020 | 7 |
2017 | Other | Auckland | Female | Asian | Other | 00-04 | 2020 | Annual | 5380 | 8 |
2017 | Other | Auckland | Female | Asian | Other | 00-04 | 2013_2014 | Financial | 3660 | 1 |
2017 | Other | Auckland | Female | Asian | Other | 00-04 | 2014_2015 | Financial | 3700 | 2 |
2017 | Other | Auckland | Female | Asian | Other | 00-04 | 2015_2016 | Financial | 3880 | 3 |
2017 | Other | Auckland | Female | Asian | Other | 00-04 | 2016_2017 | Financial | 4140 | 4 |
2017 | Other | Auckland | Female | Asian | Other | 00-04 | 2017_2018 | Financial | 4430 | 5 |
2017 | Other | Auckland | Female | Asian | Other | 00-04 | 2018_2019 | Financial | 4800 | 6 |
I want to create a measure that will allow me to calculate % Population change using the Period as the visual axis. Period comes as a text field so I created the Period Index. The visual will always have Period Type filter set to either Annual or Financial
Here is link to the pbix Population Data for full data set.
Any help gratefully received
Solved! Go to Solution.
hi, @sperry
After my test on your sample data, you could use this formula to add a measure:
Measure = VAR __Perindex = CALCULATE ( SUM ( 'Table'[Population] ), FILTER ( ALLEXCEPT ( 'Table', 'Table'[Period Type] ), 'Table'[Period Index] = MAX ( 'Table'[Period Index] ) - 1 ) ) RETURN DIVIDE ( SUM ( 'Table'[Population] ) - __Perindex, __Perindex )
By the way: If you want to add other fields as group, add them to the red font section.
Result:
and here is pbix file, please try it.
Best Regards,
Lin
hi, @sperry
You could get it by the same logic,
if the format of [Period] column is a number, you could use formula like this:
Measure = VAR __Perindex = CALCULATE ( SUM ( 'Table'[Population] ), FILTER ( ALL ( 'Table'), 'Table'[Period] = MAX ( 'Table'[Period] ) - 1 ) ) RETURN DIVIDE ( SUM ( 'Table'[Population] ) - __Perindex, __Perindex )
Else you need to add another index column
Period | Period Index | Population | Population Change from Base | new index |
2013 | 1 | 200 | 1 | |
2014 | 3 | 300 | 50.00% | 2 |
2015 | 4 | 350 | 75.00% | 3 |
2016 | 5 | 400 | 100.00% | 4 |
2017 | 6 | 450 | 125.00% | 5 |
and then use new index column for calculation.
Best Regards,
Lin
hi, @sperry
After my test on your sample data, you could use this formula to add a measure:
Measure = VAR __Perindex = CALCULATE ( SUM ( 'Table'[Population] ), FILTER ( ALLEXCEPT ( 'Table', 'Table'[Period Type] ), 'Table'[Period Index] = MAX ( 'Table'[Period Index] ) - 1 ) ) RETURN DIVIDE ( SUM ( 'Table'[Population] ) - __Perindex, __Perindex )
By the way: If you want to add other fields as group, add them to the red font section.
Result:
and here is pbix file, please try it.
Best Regards,
Lin
Excellent thanks for this..have added the groups as suggested.
One additional query. Is there a way of adding a base year (ie. Period Index = 1) and doing a calculated change from that point? To give something like this?
Period | Period Index | Population | Population Change from Base |
2013 | 1 | 200 | |
2014 | 3 | 300 | 50.00% |
2015 | 4 | 350 | 75.00% |
2016 | 5 | 400 | 100.00% |
2017 | 6 | 450 | 125.00% |
hi, @sperry
You could get it by the same logic,
if the format of [Period] column is a number, you could use formula like this:
Measure = VAR __Perindex = CALCULATE ( SUM ( 'Table'[Population] ), FILTER ( ALL ( 'Table'), 'Table'[Period] = MAX ( 'Table'[Period] ) - 1 ) ) RETURN DIVIDE ( SUM ( 'Table'[Population] ) - __Perindex, __Perindex )
Else you need to add another index column
Period | Period Index | Population | Population Change from Base | new index |
2013 | 1 | 200 | 1 | |
2014 | 3 | 300 | 50.00% | 2 |
2015 | 4 | 350 | 75.00% | 3 |
2016 | 5 | 400 | 100.00% | 4 |
2017 | 6 | 450 | 125.00% | 5 |
and then use new index column for calculation.
Best Regards,
Lin
User | Count |
---|---|
131 | |
74 | |
70 | |
58 | |
53 |
User | Count |
---|---|
190 | |
97 | |
67 | |
62 | |
54 |