If statement after grouping and aggregating

[#1]

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

[sunny_talwar]

May be just this:

RangeMin(Sum([Capped Amount], Sum(Amount))

[#3]

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.

[#4]

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.

[sunny_talwar]

Would you be able to share a sample where we can see the issue?

[sunny_talwar]

Or may be try this:

Sum(Aggr(RangeMin(Sum([Capped Amount], Sum(Amount)), [Case ID]))