Suming a column based on unique value of another column

[Anonymous]

I have a table with policy number, and total members associated to that policy number.

Problem is, the policy number and total member count is duplicated because of other categories the data is split into.

An example of my data below. 

I need to sum Member Count, in DAX, but I want it to sum based on distinct values of Policy_Nbr. So in the below example my total should be 821 and not 1,334. Is this possible?

Policy_Nbr   Member_Count   Duration

8200201      144                       11

8200201      144                       10

8200201      144                       2

306AUS1     225                       7

306AUS1     225                       1

306EGY1      452                      1

[Jihwan_Kim · Accepted solution]

Hi, @Anonymous 

Please check the below picture and the measure, whether it is what you are looking for.

Members Count Distinct Policy Nr =
VAR distincttable =
GROUPBY (
Data,
Data[Policy_Nbr],
"@membercount", MAXX ( CURRENTGROUP (), Data[Member_Count] )
)
RETURN
SUMX ( distincttable, [@membercount] )

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: https://www.linkedin.com/in/jihwankim1975/