Consider Different Categories as One to Obtain Cumulative Total

[nallaa]

Hello!

I am trying to calculate Cumulative Sum for Population against 5 variables. 

I was able to get the desired result using a custom measure with out considering variables, 'Month' and 'Week Number'.

Cumulative Population Total = SUMX(SUMMARIZE('Table','Table'[Year],'Table'[County],'Table'[Age ],'Table'[Population]),'Table'[Population])

But, I need a Custom Measure, that can take into account all 5 variables (including Month and Week Number) to dynamically calculate Cumulative Population Total based on Filter selections.

Any help is highly appreciated!

Below is the sample data table used in Power BI Desktop page,

Year	County	Age	Month	Week Number	Population
2019-2020	Washington	Child	January	1	23
2019-2020	Washington	Child	January	2	23
2019-2020	Washington	Child	February	5	23
2019-2020	Washington	Child	February	6	23
2019-2020	Washington	Child	March	9	23
2019-2020	Washington	Adolescent	February	5	30
2019-2020	Washington	Adolescent	February	6	30
2019-2020	Washington	Adolescent	March	9	30
2019-2020	Washington	Adolescent	March	10	30
2019-2020	Kent	Child	January	3	18
2019-2020	Kent	Child	February	5	18
2019-2020	Kent	Child	February	7	18
2019-2020	Kent	Child	March	9	18
2019-2020	Kent	Child	March	11	18
2019-2020	Kent	Adult	March	10	26
2019-2020	Kent	Adult	March	11	26
2019-2020	Kent	Adult	April	13	26
2019-2020	Kent	Adult	April	14	26

Thank you

Nallaa

[Ashish_Mathur]

Hi,

I do not understand your question/requirement.

[nallaa]

Hi @Ashish_Mathur 

I am sorry that my question is not clear. 

My requirement is to calculate Cumulative Total population with a custom measure that includes variables of Year, County, Age, Month, Week.

Below is the formula I have used,

Cumulative Population_Month and Week Included = SUMX(SUMMARIZE('Table','Table'[Year],'Table'[County],'Table'[Age ],'Table'[Month],'Table'[Week Number],'Table'[Population]),'Table'[Population])

The problem with this is formula is that it is considering duplicates under population column. Population values do not change for Month and Week. Population values change by Age, County and Year.

I need a formula where I retain all the variables (Year, County, Age, Month, Week) in the custom measure to get 97 as total. 

Hope this clear!

Thank you,

Abhinav

[v-easonf-msft · Accepted solution]

Hi , @nallaa 

 From your formula, I didn't find any errors.

If you just want to change the total value of the measure in the table, you can consider using  function"SUMMARIZE"   and "HASONEVALUE"

Cumulative Population_Month and Week Included 2 =
VAR a = [Cumulative Population Total]
VAR b = [Cumulative Population_Month and Week Included]
RETURN
    IF ( HASONEVALUE ( 'Table'[Index] ), b, a )

You can refer to this related post for more details about measure total level calculation issue.

Measure-Totals-The-Final-Word 

Best Regards,
Community Support Team _ Eason