Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
May be just this:
RangeMin(Sum([Capped Amount], Sum(Amount))
Hi Sunny,
Thanks but this is not capping my master measure. I know it is doing something when there are other dimensions, i.e. in a bar chart I see different numbers but my master measure (if I place put this measure on a KPI visualization) is still showing the uncapped amount.
Sorry for the late reply, I was out of town.
Regards,
SS.
Hello,
Sunny? Any answer on this?
Also please let me know if there is any other solution. Or, where am I going wrong in my formula?
Thanks,
SS.
Would you be able to share a sample where we can see the issue?
Or may be try this:
Sum(Aggr(RangeMin(Sum([Capped Amount], Sum(Amount)), [Case ID]))