Maximum version of each class in data

[shashiPaul1570]

 Hi All, 

I am working on insurance data where I have columns like Policy which is divided into different classes and each class has different versions. No I need the latest version of each class of policy.  For your reference, here is the small data. 

For your reference here is small dataset 

Column A - Policy No

Column B - Risk Class Code

Column C - Version No

Column D - Premium.

Now for example, if I have policy 1001-1, I need to check each risk class code like I have  7 classes here and for first class, I have version 3 the latest one and this version (3) is split into 2 precentage amounts, so here I need to take 1637.66 and 1091.77 (both will add up to 2729.23 and the previous versions 1 & 2  will be nullified)..... if there is highest version only one time, that means it is not spilt and we will just take the latest one.  I have tried in query as well as with Maxx() however not getting right result.  Any suggession will be appreciable. 

Policy No_Version No_Risk Class CodeBase Premium

1001-1	1	1	2729.43
1001-1	2	1	-2729.43
1001-1	3	1	1637.66
1001-1	3	1	1091.77
1001-1	1	2	170.87
1001-1	2	2	-170.87
1001-1	3	2	170.87
1001-1	1	3	30
1001-1	2	3	-30
1001-1	3	3	30
1001-1	1	4	100
1001-1	2	4	-100
1001-1	3	4	100
1001-1	1	5	1075.2
1001-1	2	5	-1075.2
1001-1	3	5	645.12
1001-1	3	5	430.08
1001-1	1	6	120
1001-1	2	6	-120
1001-1	3	6	120
1001-1	1	7	50
1001-1	2	7	-50
1001-1	3	7	50
1002-1	1	1	875.28
1002-1	2	1	-875.28
1002-1	3	1	525.17
1002-1	3	1	350.11
1002-1	1	2	132.91
1002-1	2	2	-132.91
1002-1	3	2	132.91
1002-1	1	3	27.06
1002-1	2	3	-27.06
1002-1	3	3	27.06
1002-1	1	4	117.64
1002-1	2	4	-117.64
1002-1	3	4	117.64
1002-1	1	5	225.88
1002-1	2	5	-225.88
1002-1	3	5	135.53
1002-1	3	5	90.35
1002-1	1	6	141.18
1002-1	2	6	-141.18
1002-1	3	6	141.18
1002-1	1	7	58.82
1002-1	2	7	-58.82
1002-1	3	7	58.82

 Hi All, 



I am working on insurance data where I have columns like Policy which is divided into different classes and each class has different versions. No I need the latest version of each class of policy.  For your reference, here is the small data. 

For your reference here is small dataset 

Column A - Policy No

Column B - Risk Class Code

Column C - Version No

Column D - Premium.

Now for example, if I have policy 1001-1, I need to check each risk class code like I have  7 classes here and for first class, I have version 3 the latest one and this version (3) is split into 2 precentage amounts, so here I need to take 1637.66 and 1091.77 (both will add up to 2729.23 and the previous versions 1 & 2  will be nullified)..... if there is highest version only one time, that means it is not spilt and we will just take the latest one.  I have tried in query as well as with Maxx() however not getting right result.  Any suggession will be appreciable. 

Policy No_Version No_Risk Class CodeBase Premium

1001-1	1	1	2729.43
1001-1	2	1	-2729.43
1001-1	3	1	1637.66
1001-1	3	1	1091.77
1001-1	1	2	170.87
1001-1	2	2	-170.87
1001-1	3	2	170.87
1001-1	1	3	30
1001-1	2	3	-30
1001-1	3	3	30
1001-1	1	4	100
1001-1	2	4	-100
1001-1	3	4	100
1001-1	1	5	1075.2
1001-1	2	5	-1075.2
1001-1	3	5	645.12
1001-1	3	5	430.08
1001-1	1	6	120
1001-1	2	6	-120
1001-1	3	6	120
1001-1	1	7	50
1001-1	2	7	-50
1001-1	3	7	50
1002-1	1	1	875.28
1002-1	2	1	-875.28
1002-1	3	1	525.17
1002-1	3	1	350.11
1002-1	1	2	132.91
1002-1	2	2	-132.91
1002-1	3	2	132.91
1002-1	1	3	27.06
1002-1	2	3	-27.06
1002-1	3	3	27.06
1002-1	1	4	117.64
1002-1	2	4	-117.64
1002-1	3	4	117.64
1002-1	1	5	225.88
1002-1	2	5	-225.88
1002-1	3	5	135.53
1002-1	3	5	90.35
1002-1	1	6	141.18
1002-1	2	6	-141.18
1002-1	3	6	141.18
1002-1	1	7	58.82
1002-1	2	7	-58.82
1002-1	3	7	58.82

[v-jianboli-msft · Accepted solution]

Hi @shashiPaul1570 ,

Please try:

Column = 
VAR _a =
    CALCULATE (
        MAX ( 'Table'[No_Version] ),
        FILTER (
            'Table',
            [Policy] = EARLIER ( 'Table'[Policy] )
                && [No_Risk Class Code] = EARLIER ( 'Table'[No_Risk Class Code] )
        )
    )
RETURN
    IF (
        [No_Version] = _a,
        MAXX (
            FILTER (
                'Table',
                [Policy] = EARLIER ( 'Table'[Policy] )
                    && [No_Risk Class Code] = EARLIER ( 'Table'[No_Risk Class Code] )
                    && [No_Version] = _a
                    && [Base Premium] = EARLIER('Table'[Base Premium])
            ),
            [Base Premium]
        ),
        0
    )

Output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-jianboli-msft]

Hi @shashiPaul1570 ,

Please try:

Column =
VAR _a =
    CALCULATE (
        MAX ( 'Table'[No_Version] ),
        FILTER (
            'Table',
            [Policy] = EARLIER ( 'Table'[Policy] )
                && [No_Risk Class Code] = EARLIER ( 'Table'[No_Risk Class Code] )
        )
    )
RETURN
    IF (
        [No_Version] = _a,
        SUMX (
            FILTER (
                'Table',
                [Policy] = EARLIER ( 'Table'[Policy] )
                    && [No_Risk Class Code] = EARLIER ( 'Table'[No_Risk Class Code] )
                    && [No_Version] = _a
            ),
            [Base Premium]
        ),
        0
    )

Output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-jianboli-msft]

Hi @shashiPaul1570 ,

If you want a measure, please try:

Measure = 
VAR _a =
    CALCULATE (
        MAX ( 'Table'[No_Version] ),
        FILTER (
            ALL ( 'Table' ),
            [Policy] = MAX ( 'Table'[Policy] )
                && [No_Risk Class Code] = MAX ( 'Table'[No_Risk Class Code] )
        )
    )
RETURN
    SUMX (
        FILTER (
            ALL ( 'Table' ),
            [Policy] = MAX ( 'Table'[Policy] )
                && [No_Risk Class Code] = MAX ( 'Table'[No_Risk Class Code] )
                && [No_Version] = _a
        ),
        [Base Premium]
    )

Output:

If you want a calculated column, please try:

Column = 
VAR _a =
    CALCULATE (
        MAX ( 'Table'[No_Version] ),
        FILTER (
            'Table',
            [Policy] = EARLIER('Table'[Policy] )
                && [No_Risk Class Code] = EARLIER('Table'[No_Risk Class Code] )
        )
    )
RETURN
    SUMX (
        FILTER (
            'Table',
            [Policy] =EARLIER('Table'[Policy] )
                && [No_Risk Class Code] =EARLIER('Table'[No_Risk Class Code] )
                && [No_Version] = _a
        ),
        [Base Premium]
    )

Output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[shashiPaul1570]

Hi Jianboli, 
I truly appreciate your help on this, we are too close. Now the problem I observed is, that it is showing the total for a policy which is split in version. e.g Policy 1001-1 there is version 3 which is highest and result should show $1637.66, $1091.77 instead of 2729.43 for same policy of version 3.

[shashiPaul1570]

Hi Jianboli ....... thanks for your reply however I find that we are getting the data for each version and I need to keep the maximum one only. I guess I need to create another column where I can apply condition to show data only if it is high version and else zero