Unique values

[Erika16]

Hello

Hope you can help me. The first two columns below are my data. Each client manager as a minimum capacity of clients. I need to aggregate the total by client manager but the way I have the data the value repeats in several rows. This is because the data comes from the system.  so when I try to sum the totals power BI sums the total but what I need is a unique value by client manager and then do the total. Any advice in how I can do that?

Client Manager	Min capacity of Clients	Power BI total	What I need is the unique value by client manager
Anna	30
Anna	30
Anna	30	90	30
Paul	50
Paul	50
Paul	50
Paul	50
Paul	50	250	50
Siliva	45
Siliva	45	90	45
		430	125

[TomMartens · Accepted solution]

Hey @Erika16 ,

first I recommend to remove duplicate rows using Power Query.

If this is not possible, then the below measure creates what you are looking for:

Measure = 
SUMX(
    VALUES( 'Table'[Client Manager] )
    , CALCULATE( AVERAGE('Table'[Min capacity of Clients] ) )
)

And a table visual:

Hopefully, this provides what you are looking for.

Regards,

Tom

[Erika16]

@TomMartens  can I please ask you an easy one. How do I take out the decimals. I need only the value. I do appreciate your help!!! 😁

[TomMartens · Accepted solution]

Hey @Erika16 ,

select the measure in the fields list and adjust the formatting:

Hopefully, this provides what you are looking for.

Regards,
Tom

[Erika16]

I very much appreciate your help @TomMartens  😀

[Erika16]

Many thanks Tom!! It worked 😁

[TomMartens · Accepted solution]

Hey @Erika16 ,

first I recommend to remove duplicate rows using Power Query.

If this is not possible, then the below measure creates what you are looking for:

Measure = 
SUMX(
    VALUES( 'Table'[Client Manager] )
    , CALCULATE( AVERAGE('Table'[Min capacity of Clients] ) )
)

And a table visual:

Hopefully, this provides what you are looking for.

Regards,

Tom