How to add two rows by a First name

[Anonymous]

I want to add up rows by First Name. So wherever there is a first name repeated I want to add them up so that the calculation of the rows give me an effort value of 1. For example I want to add the effort   by the first name Chandni which is repeated.

[danextian]

Hi @Anonymous ,

You may use ALLEXCEPT() so your formula should be something like:

Measure =
CALCULATE ( <aggregation>, ALLEXCEPT ( Table, Table[FirstName] ) )

But Power BI automatically aggregages values whenever they are added to a visual. Power BI sums up, if applicable the values of a column. If you add First Name and Effort Located to a table, it will show you the total Effort Allocated for a each unique first name unless another column is added that increases an aggregation's granularity such as same first names but different last names.

[Anonymous]

What formula should i use to write the same for a column. I will be using that for my traffic light system then

[danextian]

Try this as a measure

CALCULATE (
    SUM ( 'Table'[Effort Allocated] ),
    ALLEXCEPT ( 'Table', 'Table'[First Name] )
)

Replace Table with the actual table name. 

[Anonymous]

ITS GIVING ME WRONG RESULTS:

[danextian]

Hi @Anonymous ,

Please elaborate.

[Anonymous]

So it should 1 but its showing different sum 

[danextian]

Please show me a screenshot of your formula on the formula bar. It seems to me that you have used ALL instad of ALLEXCEPT.

[Anonymous]

@danextian  Usually the effort should not exceed 1

[Anonymous · Accepted solution]

I just used pivoting and it is working now