Formula query

[JoseGarcia]

Hi, 
 I am using this formula to calculate a sum.
The formula only works when I make a selection.
I would like the formula to also show overall sum value if no selection is made.

IF(not Match(Trim(ListItemsFromID_Project),'Heathrow AMP', 'Please select'),sum(master_FPR),Null())

Any ideas, help! 

Thanks  a million!

[rui24]

Hi JoseGarcia

When you don't identifier atomically assumes {$} which means only do what is in the selection.

Try put {1} which means all of your pretended field, and ignore the selections,

Try this:

IF(not Match(Trim(ListItemsFromID_Project),'Heathrow AMP', 'Please select'),sum({1}master_FPR))

Say some feed back if you can or not.

[JoseGarcia]

Hi there, 
it did not work. 
it is only summing when a selection is made.

[rui24]

Your  code below just work with selections.

IF(not Match(Trim(ListItemsFromID_Project),'Heathrow AMP', 'Please select')

To do what you want you just need this:

sum({1}master_FPR))

And this will solve your problem

[JoseGarcia]

yes that is correct. but i need to exclude heathrow AMP from my dimension.

[rui24]

Try this

Sum({1}{< ListItemsFromID_Project -= 'heathrow AMP' >} master_FPR)

[JoseGarcia]

nearly there 🙂
The sum with no selection is made is working. However when I select heathrow amp, this should not show any values, it does show. just need to exclude heathrow amp from the dimension 🙂

[rui24 · Accepted solution]

See if this works for you

IF((ListItemsFromID_Project = 'heathrow AMP'),
'-',
Sum({1}{< ListItemsFromID_Project -= 'heathrow AMP' >} master_FPR)
)

OR MORE SIMPLE:

IF((ListItemsFromID_Project = 'heathrow AMP'),
'-',
Sum(master_FPR)
)

[JoseGarcia]

no Luck, Heathrow AMP value still showing when selected 😞

[rui24]

100% sure it is:

 This what you want to not show ?     ListItemsFromID_Project = 'heathrow AMP'