Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello there,
I need help in with a method to achieve the results I need for the following 'Policy' table:
I have already filtered policies by date filters(Year/Month), then I want to calculate sum of the 'Amount' for the listed policies where revision is highest for the 'policy_Number'.
Year | Month | Day | Policy_Number | Revision | Amount |
2019 | January | 7 | P1726 | 2 | $0 |
2019 | January | 11 | P1726 | 3 | $22,802 |
2019 | January | 28 | P1726 | 4 | $22,802 |
2019 | January | 7 | P1913 | 3 | $3,823 |
2019 | January | 23 | P002275 | 0 | $110,276 |
The expected result should be $136,901 becuase I want to sum 'Amount' of the following policies as their revisions are highest
2019 | January | 28 | P1726 | 4 | $22,802 |
2019 | January | 7 | P1913 | 3 | $3,823 |
2019 | January | 23 | P002275 | 0 | $110,276 |
Thanks for the help in advance!
Try Something like
Measure =
VAR __id = MAX ( 'Table'[Policy_Number] )
VAR __date = CALCULATE ( MAX( 'Table'[Date] ), ALLSELECTED ( 'Table' ), 'Table'[Policy_Number] = __id )
RETURN CALCULATE ( sum ( 'Table'[Amount] ), VALUES ( 'Table'[Month-Year] ), 'Table'[id] = __id, 'Table'[date] = __date )
Thanks @amitchandak for your reply.
This method won't help as I need to get the result based on the latest 'Revision' for a given 'Policy_Number' and then sum all policies 'Amount' Could you please suggest an alternate method.
Thanks.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |