Forum Discussion
Maximum version of each class in data
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
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.
5 Replies
v-jianboli-msftCommunity Support
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.
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
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.
- 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 ,
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.
