21 Replies Latest reply: Apr 16, 2018 11:04 AM by raj kapoor RSS

    PLEASE HELP: sum, aggregation and set analysis

    raj kapoor

      @Hi All,

       

      I have tried to create a logic using set analysis which combines both the sum and aggregation function but failed to get the right method. Here's a simple table to illustrate what I wish to achieve:

       

      I would like to count the number of distinct store ids if the total product sale for the whole month for the given store id is less than a threshold value.

       

      In the example below, the threshold value for product sale is 50.

       

      So if you count the number of STORE ID whose given Product Sale is less than 50 for the whole month, then the result is 1 (Store ID A).

       

      Please note that the table is not complete and contains data for only 4 days.

       

      I've tried this function in a text box but I'm getting errors:

       

      =count( aggr(sum({$<[City] = {'London'}, [Month]={'Jan'}}[Store ID])<50) DISTINCT [Store ID])

       

      Not very sure whether that's the right logic. I would really appreciate your help on this one. Thanks

         

      Store IDMonthDateProduct SaleCity
      AJan01/01/201810London
      AJan02/01/20185London
      AJan03/01/201810London
      AJan04/01/20185London
      AFeb01/02/201896London
      AFeb02/02/201820London
      AFeb03/02/201815London
      AFeb04/02/201830London
      BJan01/01/201861London
      BJan02/01/201813London
      BJan03/01/201825London
      BJan04/01/201821London
      BFeb01/02/201896London
      BFeb02/02/201820London
      BFeb03/02/201815London
      BFeb04/02/201830London