Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

Webi formula: Create sum with where clause on measure

Hi,

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:

DimensionMeasure
A12
B8
C25
D3
E11

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,

Stefan

replied

Hi Stefan,

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.

Try:

=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!

Carly

(had to edit, realized I left in my testing #s for eFashion Sales Revenue)

0 View this answer in context

Helpful Answer

by
Not what you were looking for? View more on this topic or Ask a question