Enable filters for aggregate functions

[kxie]

It would be extremely helpful to allow filters for aggregate functions.

Use Case:

In order to calculate the percentage of total sales for category A, I need to sum sales filtered by category A and divide by the sum of sales. Currently, it works to use if statements for summations, but this workaround can't be used for aggregate functions like average. 

[ZachBreimayer]

Hi Kxie,

For this use case it may be more ideal to use a groupBy function approach as conditional(if) statements are generally going to be more resource intense, meaning latency. Other aggregate functions(avg etc) will work with this approach as well, please see as follows:

For this Im using our 'OnlineStore' sample data, if your familiar with that you may be with Adventureworks sample database, very similar. 

This scenario I want to see the contribution by product category, we see in image1 below that an aggregate table is being used with Category as the grouping dimension, Revenue as a measure(for context) and a formula field Contribution%(This shows the percentage to total sales). 

image1

The Contribution% we need to divide the sum or grouped sales by the total sales and of course if we are applying filters we want that in context.

image2 shows the formula below

image2

The first part of the formula 'SUM(Online_Store.SalesWeather.Revenue)' respects the grouping dimension meaning it will return the total for each category, while the second part 'SUM(Online_Store.SalesWeather.Revenue,groupBy())' aggregates the total as the groupBy function is empty.

Now if I come out of edit mode I can see the contribution by product category

Now if I exclude bikes via filtering, it respects the filter the total excludes bikes and the contribution% across the remaining categories. 

Similarly you can replace sum with average in that formula and leverage that approach for the variance or any other measures you may be trying to calculate.

Id also recommend taking a quick read through https://docs.incorta.com/6.0/references-built-in-functions-aggregation

[kxie]

Can this be used to count distinct transactions of various types?

For example, calculate the distinct transactions in category A divided by the distinct transactions in category B?

Edit:

Clarification on the use case: I am looking to perform a calculation of where both the numerator and denominator have separate filters that are not in the grouping dimension.