Sum of an aggregation if the aggregation meets a calculated threshold

[#1]

Hi Community,

I am trying to get the sum sales of all customers that have total sales above a certain level. The issue I'm running into is that the data is at a transactional level, so the formula must first aggregate sales at the customer level and then assess those aggregated values.

I have tried the following without luck:

IF(

     AGGR(SUM(Sales), Customer) >= v.CUSTFRACTILE,

     SUM(SALES))

and

IF(

     AGGR(SUM(Sales), Customer) >= v.CUSTFRACTILE,

     SUM(AGGR(SUM(Sales), Customer)))

Any help would be greatly appreciated!

Thank you,

[vishsaggi]

Can you share some sample data with an expected output.

[#3]

Hi Vishwarath,

I can't share the actual data but I have created a simple example below:

If in this example I'm looking for the sum of sales for all customers that have sales of at least 400, the correct answer would be 1430 (sum of sales for customers B and C since they spent 430 and 1000 respectively).

[vishsaggi]

Are you looking for this?

Assuming that you already have set the variable vCustfractile to 400.

Then in your straight table add Customer and Transaction and expression as

= IF(Sum(Aggr(Sum(SalesAmt), Customer)) >= vCustfractile, Sum(Aggr(Sum(SalesAmt), Customer)))

With Total Mode as Sum of Rows Radio check.

[#5]

Thanks, Vishwarath.

I'm looking to get the number to display in a text  box since I'm using it as a KPI at the top of a dashboard. Is there a way of calculating this without using a straight table (or alternately referencing a straight table total from a text box)?

[AxNoxy · Accepted solution]

Something like this ?

Regards!

[#7]

The SUM(IF( statement works perfectly - Thank you!

[suryaa30]

How to get this 1430 in a text box using set expression?  assuming we have vCUSTFRACTILE as variable.

[sunny_talwar]

May be this

=Sum({<Cutomer = {"=Sum(Sales) > $(vCUSTFRACTILE)"}>}Sales)

[suryaa30]

Perfect thanks Much Sunny.