Can I combine {1} with other set analysis for SUM function?

[JoshPark]

Hi everyone,

Is there any way to ignore the selection but keeping one or more conditions for the SUM function? 

For example, as my attached app, when I select "International", I expect the % is 77% (= 3.86 / 5.01)

But I think my expression is not correct, which show 51% (= 2.86 / 7.54)

Here is the expression I use:

Sum({<[Date.autoCalendar.Year] = {'2022'}>} Sales) 
/ (Sum({1} Sales) 
	- Sum({<[Date.autoCalendar.Year] = {'2019'}>} Sales)
    - Sum({<[Date.autoCalendar.Year] = {'2020'}>} Sales)
    - Sum({<[Date.autoCalendar.Year] = {'2021'}>} Sales)
    - Sum({<[Date.autoCalendar.Year] = {'2023'}>} Sales))

Update: It means if I choose "Domestic" I expect to get the result (automatically) 23% ( = 1.14 / 50.1)

Can anyone help me?

Many Thanks!

[ogster1974 · Accepted solution]

Sum({<[Date.autoCalendar.Year] = {'2022'} >} Sales)
/ Sum({<[Date.autoCalendar.Year] = {'2022'}, [Region (SAP)]=>}Sales)

will get you 77%

[JoshPark]

Hi @ogster1974 

Thank you for your suggestion. 

But I would like to get the result in accordance with my selection.

It means if I choose "Domestic", I hope the result would (automatically) be 23% ( = 1.14 / 5.01)

Do you have any ideas?

[ogster1974]

It does produce that result if you've applied the filter like below...

[JoshPark]

Hi @ogster1974 

Thank you so much! I thought set analysis must go with a fixed value so i put {'International'}. 

Then can you kindly help to review another sheet in the app. 

How can I display the percentage based on multiple selections? 

I have all 3 treemap with according %

1st bullet chart 

Sum(Sales) / Sum({1} Sales)

2nd bullet chart  

Sum({<[Region (SAP)]=>} Sales) / Sum({1} Sales)

3rd bullet chart 

Sum({<[Region (SAP)]=, Country=>} Sales) / Sum({1} Sales)

In 1st treemap, if I choose "International", I should get:

• 71% = (9.06 / 3.68)
• 100%
• 100%

In 2nd treemap, if I choose "US", I should get

• 71% = (9.06 / 3.68)
• 32% = (2.94 / 9.06)
• 100%

In 3rd treemap, if I choose "USB", I should get

• 71% = (9.06 / 3.68)
• 32% = (2.94 / 9.06)
• 42% = (1.23 / 2.94)

But the results I get are as follow:

Could you please help me check where I have done wrong?

Thank you!

[ogster1974]

Post this as another question for others to contribute on