# 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:

Dimension | Measure |
---|---|

A | 12 |

B | 8 |

C | 25 |

D | 3 |

E | 11 |

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

##### Carly Thomas 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)