Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

How to add two rows by a First name

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.

 

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    I just used pivoting and it is working now

9 Replies

  • danextian's avatar
    danextian
    Super User
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

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

      • danextian's avatar
        danextian
        Super User
        6 years ago

        Try this as a measure

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

        Replace Table with the actual table name.