@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 ID | Month | Date | Product Sale | City |
| A | Jan | 01/01/2018 | 10 | London |
| A | Jan | 02/01/2018 | 5 | London |
| A | Jan | 03/01/2018 | 10 | London |
| A | Jan | 04/01/2018 | 5 | London |
| A | Feb | 01/02/2018 | 96 | London |
| A | Feb | 02/02/2018 | 20 | London |
| A | Feb | 03/02/2018 | 15 | London |
| A | Feb | 04/02/2018 | 30 | London |
| B | Jan | 01/01/2018 | 61 | London |
| B | Jan | 02/01/2018 | 13 | London |
| B | Jan | 03/01/2018 | 25 | London |
| B | Jan | 04/01/2018 | 21 | London |
| B | Feb | 01/02/2018 | 96 | London |
| B | Feb | 02/02/2018 | 20 | London |
| B | Feb | 03/02/2018 | 15 | London |
| B | Feb | 04/02/2018 | 30 | London |
