Hello folks,
I have two tables in my data model, both connected with a primary key called "Case ID"
In table 1, I have a bunch of Case IDs and their amounts. In table 2, I have a few of the Case IDs from table 1 (not all) with a cap on the amount.
Looking at the below tables, the amount of case ID 1 is 27000, however according to table 2, the case ID 1 should be capped at 17000, how can I achieve this?
I tried the below formula but it did not work. The 2 Case IDs are associated in the data model.
|
IF(AGGR(SUM([Amount]),[Case ID])>SUM([Capped Amount]),SUM([Capped Amount]),SUM([Amount]))/
|
Table 1:
| TimeSheet ID | Amount | Case ID |
|---|
| 10992 | 10000 | 1 |
| 10993 | 15000 | 1 |
| 10994 | 2000 | 1 |
| 10995 | 6000 | 2 |
Table 2:
| Case ID | Capped Amount |
|---|
| 1 | 17000 |
| 3 | 6000 |
