Forum Discussion
Unique values
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 |
Hey Anonymous ,
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
Hey Anonymous ,
select the measure in the fields list and adjust the formatting:
Hopefully, this provides what you are looking for.Regards,
Tom
5 Replies
Hey Anonymous ,
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
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!!! 😁
Hey Anonymous ,
select the measure in the fields list and adjust the formatting:
Hopefully, this provides what you are looking for.Regards,
TomI very much appreciate your help TomMartens 😀