cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Helper V

## DISTINCTCOUNT group totals

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!

1 ACCEPTED SOLUTION
Helper V

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!

4 REPLIES 4
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Helper V

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?

Helper V

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!

Community Champion

@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] ) )
)
)```

Lima - Peru

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors