hierarchy slicer summing totals for category, not sub-category

[Anonymous]

Hello, 

I am using a hierarchy slicer to enable the selection of different years (sub-categories) by group (categories).   I want to calculate a total for each group that only includes the values for the selected years.  However, when I select any 1 year sub-category the sum total includes all previous year values for that same group.   

You can see in my table visual that the 2 selected groups for Year 2025 are $12,000,000 + $37,639,019 = ~$49,639,019.  However, my total is $60,146,789.  This is the total for ALL years for the Children <5 group, however I only want the total for the years selected (2022-2025).  

Any thoughts on how to solve this? My file is here. 

Thanks in advance to anyone who can help to solve this issue.  I have been really struggling to sort it out. 

Kind regards, Robert 

[v-juanli-msft]

Hi @Anonymous 

What is the purpose of measure "Incremental Alt"?

Should it sum values for all groups per year or sum values for all year per group?

Best Regards

Maggie

[Anonymous]

It should sum values for all those selected groups in the year by year. 

Thanks!

[v-juanli-msft]

Hi @Anonymous 

I make a test, but i get a strange result.

Could you show me the desired output of correct measure "Incremental Alt"?

May be you can enter the correct value in the excel below.

Vulnerable Groups	Year	Cumulative Max Alt	Incremental Alt	Incremental Alt correct
1Q HEF & Extensions	2019	$7,000,000	$7,000,000
1Q HEF & Extensions	2020	$7,000,000	$7,000,000
1Q HEF & Extensions	2021	$8,000,000	$8,000,000
1Q HEF & Extensions	2022	$9,000,000	$44,931,076
Children <5	2022	$9,192,407	$44,931,076
1Q HEF & Extensions	2023	$10,000,000	$55,266,884
Children <5	2023	$18,528,215	$55,266,884
1Q HEF & Extensions	2024	$11,000,000	$58,407,226
Children <5	2024	$28,009,662	$58,407,226
1Q HEF & Extensions	2025	$12,000,000	$60,146,789
Children <5	2025	$37,639,019	$60,146,789

Additionally, the "Cumulative Max Alt" calculate the correct result, right?

Best Regards

Maggie

[Anonymous]

Thanks Maggie, 

The correct result should be: 

May be you can enter the correct value in the excel below.

Vulnerable Groups	Year	Cumulative Max Alt	Incremental Alt	Incremental Alt correct
1Q HEF & Extensions	2019	$7,000,000	$7,000,000	$7,000,000
1Q HEF & Extensions	2020	$7,000,000	$7,000,000	$7,000,000
1Q HEF & Extensions	2021	$8,000,000	$8,000,000	$8,000,000
1Q HEF & Extensions	2022	$9,000,000	$44,931,076	$18,192,407
Children <5	2022	$9,192,407	$44,931,076	$18,192,407
1Q HEF & Extensions	2023	$10,000,000	$55,266,884	$28,528,215
Children <5	2023	$18,528,215	$55,266,884	$28,528,215
1Q HEF & Extensions	2024	$11,000,000	$58,407,226	$39,009,662
Children <5	2024	$28,009,662	$58,407,226	$39,009,662
1Q HEF & Extensions	2025	$12,000,000	$60,146,789	$49,639,019
Children <5	2025	$37,639,019	$60,146,789	$49,639,019

What is happening is the totals from Incremental Alt are adding in the previous year totals for Children<5 into the years for which it is selected.  However, I only want it to sum the amounts in that year which are calculated in Cumulative Max Alt.  

Thanks much.