Aggregate statistics with additional rows filtering - OVER function

[Grzegorz Zielinski]

In separate columns in table visualization I need to have stats (e.g. Average) calculated for all rows with additional filtering based on flags in other columns e.g. as per attached screen column D sales avg for all locations is simple Avg([sales]) OVER ([Location])but how about column E where I want to exclude from calculations rows marked with "Y"in column C Exclude

[David Boot-Olazabal]

Hi Grzegorz,

You could use this expression to calculate this average non-excluded column:Avg([sales]) OVER ([Location],[Exclude]).

I have attached the dxp as well, as an example you could look at.

Kind regards,

David

[Grzegorz Zielinski]

Thank you David!Actually this is not exactly what I'm trying to achieve, perhaps I didn't phrase my request precisely. Issue with your solution is 2nd row in the screen attached: I want there 500 for AVG and 150 for Avg non-excluded. In other words I would like to have configurable syntax that allows me to build expression with a kind of WHERE SQL equivalent e.g. calculate AVG for rows where:

a) EXCLUDE is null or/and

b) EXCLUDE is equal "Y" or "Yes" or "1" 

c) etc...

Moreover I need those results presented for each row consistently in table visualization both for Excluded and no-Excluded rows.

[Grzegorz Zielinski]

[David Boot-Olazabal]

Hi Grzegorz,

Have a look at the attached dashboard, I think that is what you are looking for.

I have replaced the Sales for rows where Excluded is not null (Y, 1 or anything) with a Null value (just as a means to calculate the average is 150). Based on that colum, I have created a new average which will show you 150 for each of Location A1.

Please test it out in a larger dataset, to check whether this is what you could use for your dashboard.

Kind regards,

David

[Grzegorz Zielinski]

Thanks again. It works as workaround. However I have a big number of columns (let's assume 100) like Sales for which I need to perform the same calculations so it means that it requires  creating 100 custom columns with CASE statement and then another 100 for AVG. Is there a more efficient way to approach that