SAP BO WEBI report function like SQL having clause

[horynao]

Hello everyone,

I would like to ask how to do with some function in WEBI report like SQL having clause? I have a dataset I like to calculate first measure (an average of sold items in sales manager dimension) and second measure (total costs per sold items on row level). But if I filter second measure the first one is recalculated. I tried to use NoFilter function, but I like to keep one dimension report filter. For this one dimension it is all right, that the measures are recalculated.

Thanks in advance

Best Regards

Ondrej

[ayman_salem]

Can you provide a screenshot with sample data so we can properly understand your issue and give you a possible correct answer

[horynao]

Hello,

yes of course, there is my sample dataset.

row_id;datum;prodavac;kategorie;produkt;pocet;cena
11;2020-2;Pepa;místní;jablka;10;15
12;2020-10;Pepa;místní;hrušky;3;12
13;2020-2;Adam;místní;švestky;5;14
14;2020-12;Adam;cizokrajné;pomeranče;10;5
15;2020-4;Pepa;cizokrajné;banány;7;7
16;2020-8;Adam;cizokrajné;manga;6;7
17;2020-7;Pepa;cizokrajné;ananas;1;20
18;2020-3;Adam;cizokrajné;pomelo;2;15
19;2020-11;Pepa;místní;švestky;8;14
20;2020-6;Pepa;cizokrajné;liči;8;5

And as I wrote, I need to calculate first measure (an average of sold items in sales manager dimension) and second measure (total costs per sold items on row level). But the first measure needs to by calculate after filter of kategorie = "cizokrajné" is applied. And then I need to filter table with both measures limits. I imagine this part as like SQL having clause. SAP BO probably needs to know, what to calculate first.

The first measure.

The second measure

I hope my issue would be more clear now.

Thank you in advance

Ondrej

[horynao]

Hello everyone,

would have someone another advice?

Thank you in advance,

Ondrej

[krisa24]

Hi this should work:

[first measure] = average ( [sold items]) where (category = "cizokrajné") In ([sales manager])

[horynao]

Hello, thank you. But I think the condition category = "cizokrajné" is set up hardly, user cannot change it.

Thank you,

Ondrej