Calculated percentage change based on sum of a text category

[sperry]

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

[v-lili6-msft · Accepted 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.

https://www.sqlbi.com/articles/managing-all-functions-in-dax-all-allselected-allnoblankrow-allexcept...

Result:

and here is pbix file, please try it.

Best Regards,

Lin

[v-lili6-msft · Accepted 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.

https://www.sqlbi.com/articles/managing-all-functions-in-dax-all-allselected-allnoblankrow-allexcept...

Result:

and here is pbix file, please try it.

Best Regards,

Lin

[sperry]

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%

[v-lili6-msft · Accepted solution]

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