Distinctcount

[PDRTXRA]

Hello.

I have two tables. One with clients, other with lines of consumption.

Clients table:

Client ID | Name

1             John
2             Mark
3             Sarah

Consumption table:

Consumption ID | Client ID | Partner 

1                                 2             ABC

2                                 2             DEF

3                                 1             ABC

4                                 3             ABC

5                                 3             GHI

6                                 3             JKL

I want to create a calculated column on the clients table to have the number of different partners on where they have consumed.

I thought a simple 'New Column = DISTINCTCOUNT('Consumption'[Partner]) would work but it just appears the total value of different partners in the table.

So, I want:

Client ID | Name | # Partners

1             John            1
2             Mark            2
3             Sarah           3

After that, I need the average value of partners for all clients. If there's a simpler way to get to it, I'd love to hear it aswell. 

Thank you.

[amitchandak · Accepted solution]

@PDRTXRA , Create a new measure

averageX(Values(Clients[Client ID]) , calculate(DISTINCTCOUNT('Consumption'[Partner])) )