Webi formula: Create sum with where clause on measure
I am trying to create a sum of all values in a table which are greater than a specific threshold. Maybe someone can help me here. I haven't figured whether and how this is possible.
Here is a simple example:
I would like to sum up all values that are greater than 10. So I would have 12 + 25 + 11 = 48.
The formula I tried in WebI was:
=Sum([Measure]) Where ([Measure] >= 10)
The formula is validated with no errors, but the result gives me the grand total ignoring the where clause.
In this example, that would be 12 + 8 + 25 + 3 + 11 = 59.
Any idea, how to achieve a formula like the above?
Thanks and regards,
Carly Thomas replied
If you have aggregation set on Anzahl measure at the semantic layer (Universe) or data source level, this might be why you're seeing a different.
=Sum([Anzahl] In ([Cluster]) Where ([Anzahl] In ([Cluster]) >= 90))
This will override the default aggregation you may have to avoid summing BEFORE analyzing your WHERE clause.
I hope that helps!
(had to edit, realized I left in my testing #s for eFashion Sales Revenue)