how to get distinct values of a column based on another column

[Anonymous]

Hi Everyone,

Hope everyone is safe.

I need help with developing a dax fomula that will allow me to get distinct total complaints value based on Month_Year. Following is how the data sits in the table;

This dax calculation will then be used to get calculate (Cases / total complaints) *100%

Month_Year	Issue Groups	Keywords	Cases	Total complaints
1/08/2019	Service	Debt	23	10360
1/08/2019	Payment	Payment	45	10360
1/08/2019	Credit	Charges	787	10360
1/08/2019	Cash	Fees	29	10360
1/08/2019	Delivery	Payment	26	10360
1/09/2019	Payment	Debt	34	11319
1/09/2019	Cash	Charges	43	11319
1/09/2019	Credit	Fees	47	11319
1/09/2019	Service	Damage	434	11319
1/09/2019	Delivery	Infastructure	56	11319

When I do a sum of total complaints. Following is the results I'm getting at the moment;

Month_Year	Total complaints
1/08/2019	51800
1/09/2019	56595

Following is what I desire to get;

Month_Year	Total complaints
1/08/2019	10360
1/09/2019	11319

Appreciate any idea on how this can be done. Thank you in advance.

Best Regards,

Ashan.

[amitchandak · Accepted solution]

@Anonymous , try like

sumx(Values(Table[Month_Year]), max(Table[Total complaints]))
sumx(summarize(Table,Table[Month_Year],"_1", max(Table[Total complaints])),[_1])

[Anonymous]

@amitchandak Formula you provided works. Thanks a lot. Appreciate your help.

Best Regards,

Ashan.