DISTINCTCOUNT group totals

[bullius]

Hi

Here is an example of my data structure:

Group	ID	Amount
A	1	10
B	1	20
B	2	15
A	3	25
A	4	10
B	4	30
C	4	20
A	5	10
B	6	20
A	7	10
B	7	20
A	8	20
B	8	10
A	9	10
B	10	25

I want a DISTINCTCOUNT of IDs, as a grand total and group totals.

The grand total is fine. It is 10. The problem comes when I calculate the group totals. At the moment they come out as:

A: 7

B: 7

C: 1

Combined, these equal 15. I want them to equal 10. To do this, I want to only count, for each ID, the row with the MAX Amount. The logic might be as follows:

1. Create table that chooses, for each ID, the row with the MAX Amount:

Group	ID	Amount
B	1	20
B	2	15
A	3	25
B	4	30
A	5	10
B	6	20
B	7	20
A	8	20
A	9	10
B	10	25

2. DISTINCTCOUNT ID.

This should return results that look like this:

A: 4

B: 6

C: 0

How could I do this?

Thanks!

[v-shex-msft]

Hi @bullius,

Vvelarde's formula seems well, you can also use summarize function to group the table, then use lookupvalue function to find the specify group.

Sample:

Merged = ADDCOLUMNS(SUMMARIZE( Sheet2, Sheet2[ID], "MaxAmonut", MAX(Sheet2[Amount])),"Group",LOOKUPVALUE(Sheet2[Group],Sheet2[ID],[ID],Sheet2[Amount],[MaxAmonut]))

Regards,

Xiaoxin Sheng

[bullius]

Thanks @v-shex-msft & @Vvelarde,

Unfortunately, both formulas returned the error:

"A table of multiple values was supplied where a single value was expected."

Any thoughts?

[bullius · Accepted solution]

I have discovered why this is:

There are ID's with two equal amounts for different groups. Therefore, it is returning two MAX amounts and causing the error message. I will try to resolve this and try the formulas again.

Thanks!

[Vvelarde]

@bullius

You can create a New Table with Summarize

TableMaxAmounts =
SUMMARIZE (
    Table1;
    Table1[ID],
    "MaxAmount", MAX ( Table1[Amount] ),
    "Group", CALCULATE (
        VALUES ( Table1[Group] ),
        FILTER ( Table1, Table1[Amount] = MAX ( Table1[Amount] ) )
    )
)