5 Replies Latest reply: Dec 5, 2016 5:58 AM by Sunny Talwar

# If statement after grouping and aggregating

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 IDAmountCase ID
10992100001
10993150001
1099420001
1099560002

Table 2:

Case IDCapped Amount
117000
36000
• ###### Re: If statement after grouping and aggregating

May be just this:

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

• ###### Re: If statement after grouping and aggregating

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.

• ###### Re: If statement after grouping and aggregating

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.

• ###### Re: If statement after grouping and aggregating

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

• ###### Re: If statement after grouping and aggregating

Or may be try this:

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