Forum Discussion
SUM - Only when a specific number is reached
Hi guys,
quick question:
| Customer | Number of Purchase | Amount | Year |
| A | 4 | 500 | 2019 |
| A | 3 | 400 | 2019 |
| B | 2 | 200 | 2019 |
| C | 2 | 100 | 2019 |
| A | 2 | 200 | 2019 |
| B | 1 | 300 | 2019 |
| C | 1 | 200 | 2019 |
| A | 1 | 100 | 2019 |
| B | 4 | 100 | 2018 |
| B | 3 | 100 | 2018 |
| B | 2 | 100 | 2018 |
| B | 1 | 100 | 2018 |
| C | 2 | 300 | 2018 |
| C | 1 | 300 | 2018 |
| A | 1 | 200 | 2018 |
I want to calculate the sum for each customer who has more than 3 purchases per year. Each year the counter starts at 0 again.
In 2018: Customer B has made more than 3 purchases , the sum is 400.
In 2019: Customer A has made more than 3 purchases, the sum is 1200.
All other customers have less than 3 purchases in the correspoding year and are therefore not consindered in the calculation.
Any ideas ? I need some kind of a Serach function as I suppose but this is only available for text columns.
Hello Alex
try this measure:
= SUMX( VALUES( Data[Year] ), CALCULATE( SUM( Data[Amount] ), FILTER( VALUES( Data[Customer] ), CALCULATE( COUNTROWS( Data ) ) > 3 ) ) )
3 Replies
- LivioLanzo
Solution Sage
Hello Alex
try this measure:
= SUMX( VALUES( Data[Year] ), CALCULATE( SUM( Data[Amount] ), FILTER( VALUES( Data[Customer] ), CALCULATE( COUNTROWS( Data ) ) > 3 ) ) ) - jtownsend21
Responsive Resident
Try this
Sum - Over 3 Purchases = CALCULATE( SUM('Table 1'[Amount]), FILTER(Table 1, DISTINCTCOUNT('Table 1'[Number of Purchase]) > 3 ) )
