Matching Information

[schumi1980]

Dear All

I have problems matching planning data with actual data.

The actual data are on the account level like this:

Month	Premium	Account
1	50	A
1	25	B
2	75	C
2	10	D
3	100	E
4	40	F
4	75	G

The plan data are organised like

Month	Plan Premium
1	75
2	75
3	75
4	75

I want now to compare plan and actuals on a quarterly level. I would have matched the data with month and summed up the plan premium with distinct in order to avoid double counting (which would occur for example in month 1).

However, I have now the plan for several countries. Their monthly plan premium can be the same. If country A and country B have the same plan, sum(distinct [Plan Premium]) would only get the plan premium once for a month or a quarter.

Is there a more straight forward way to match data like the above?

Many, many thanks.

Best regards,

Jan

[Anonymous]

Hi,

I think you can create a Composite key on Month and Country and autonumber this.

So something like autonumber(Month &'_'&<Country>). You would have to have this key in both table.

how to create composite key

[sunny_talwar · Accepted solution]

May be try this

Sum(Aggr(Sum(DISTINCT [Plan Premium]), Month, Country))