Jump to content

Aggregate statistics with additional rows filtering - OVER function

Grzegorz Zielinski

Recommended Posts

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
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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,


Link to comment
Share on other sites

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
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...