Sum column in measure, ignoring other columns

[Anonymous]

Dear all,

I have a column in my table which i want to be summed on a unique row, but it needs to ignore the 'code' column. So when summing instantly it returns 10. but I need it to be two, since City is changing as well, but this needs to be taken into account.

ID	TYPE	Code	City	Type_ID	Year	Sum
A	R!	625	NY	R1-A	2023	1
A	R!	545	NY	R1-A	2023	1
A	R!	126	NY	R1-A	2023	1
A	R!	120	NY	R1-A	2023	1
A	R!	116	NY	R1-A	2023	1
A	R!	625	LA	R1-A	2023	1
A	R!	545	LA	R1-A	2023	1
A	R!	126	LA	R1-A	2023	1
A	R!	120	LA	R1-A	2023	1
A	R!	116	LA	R1-A	2023	1

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

You may create a measure as below.

Sum = 
COUNTROWS(
    SUMMARIZE(
        'Table',
        'Table'[ID],
        'Table'[City]
    )
)

Result:

Best Regards

Allan

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

[amitchandak]

@Anonymous , is it not, distinct count of the city ?

a new measure

distinctcount(Table[City])

[Anonymous]

the result is 2 yes, but lets say my table in real is much longer with different ID in same city