Need Help - Query to perform summation of a column by selecting distinct rows based on a column

[kiran85]

Hi, I am very new to BI. I am trying to get the sum of column X by filtering the distinct rows based on the Column A.

My Data looks like below

RecID	BatchID	RecordCount	Remarks
201611280350010172	092860	2	ABC
201611280350010172	092860	2	llsjd
201611280350010172	092860	2	fgfd
201611280350010172	092860	2	dfg
201611280350010172	092860	2	dfg
201611280350230000	935613	3	ythe
201611280350230000	935613	3	shyht
201611280350230000	935613	3	sdr
201611280350230000	935613	3	utt
201611280350202000	057808	1	ghk
201611280350202000	057808	1	sdew
201611280350202000	057808	1	dfgj
201611280350202000	057808	1	sdfr

My requirement is to take the distinct rows based on column RecID and sum the values in column RecordCount.

so my expected value is 2+3+1 = 6.

Please advise how I can achieve this.

[Phil_Seamark · Accepted solution]

Hi @kiran85

You can create a new summary table using this DAX.  I've assumed your main table is called Table1.

You can create SUM based measures on this table that will give you the result of 6

Summary Table = SUMMARIZE(
            'Table1',
            Table1[RecID],
            "RecordCount",MAX('Table1'[RecordCount])
            )

[kiran85]

---

@Phil_Seamark wrote:

Hi @kiran85

You can create a new summary table using this DAX.  I've assumed your main table is called Table1.

You can create SUM based measures on this table that will give you the result of 6

Summary Table = SUMMARIZE(
            'Table1',
            Table1[RecID],
            "RecordCount",MAX('Table1'[RecordCount])
            )

---

Hi @Phil_Seamark. Thanks for your response. Unfortunately I am receiving an error as "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."

[Phil_Seamark]

Hi @kiran85

Did you create it as a New Table, rather than a New Measure?

[kiran85]

Thanks Phil

[Sean]

For Phil solution you have to press the New Table button and then enter the formula!

Here's an alternative using the Query Editor's Group By feature...

Both methods will give you the same result!

[kiran85]

Thanks Sean