Forum Discussion
Flag data at Certain Threshold
Hello - I am looking for some quick help here to meet a tight deadline. I am looking to flag certain customers in my data set once their aggregate $ amount hits a certain threshold (in this case $3,500). The data below is what I am looking to acheive in Power BI. Once the customers aggregate value hits the threshold I want to flag all deals from that customer going forward (Flagged column). Thank you in advance for any help provided!
| Date | Customer | Contract | $ Value | Flagged |
| 1/1/2019 | Customer A | Contract 1 | 1000 | |
| 1/1/2019 | Customer B | Contract 2 | 1000 | |
| 2/1/2019 | Customer A | Contract 3 | 2000 | |
| 2/1/2019 | Customer C | Contract 4 | 2000 | |
| 3/1/2019 | Customer A | Contract 5 | 2000 | Y |
| 3/1/2019 | Customer B | Contract 6 | 2000 | Y |
| 4/1/2019 | Customer A | Contract 7 | 500 | Y |
| 4/1/2019 | Customer C | Contract 8 | 500 |
12 Replies
Anonymous here is the measure, in your example,Customer B will not be flagged, his cummulative total is less than 3500
Flagged = VAR __value = CALCULATE( SUM( Table7[$ Value] ),FILTER( ALLEXCEPT(Table7, Table7[Customer] ), Table7[Date] <= MAX( Table7[Date] ) ) ) RETURN IF( __value > 3500, "Y" )
parry2k this works to an extent, but there are customers that have contracts that are on the same date and if the customer exceeds the limit on that date, it will take ALL of the contracts that are booked on that date and flag them all, not just that contract and future ones. Is there a way to fix that logic by chance?
Anonymous which contract will come first in the date???
