Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Calculate values one row / one columns

Hi everyone, I am working with a projectplanning dataset. We have a list of possible opportunities with different candidates dedicated to them. For each opportunity I would like to calculate the number of candidates. The data is something like this:

 

Project nameCandidates
Datalake at X10000005, 10000012, 10000008
Data ingestion at X10000005, 10000006
Impelementation at X10000007
business Intelligence at X10000009, 10000008, 10000011, 10000010

 

The desired outcome would be:

Project nameCandidatesNumber of candidates
Datalake at X10000005, 10000012, 100000083
Data ingestion at X10000005, 100000062
Impelementation at X100000071
business Intelligence at X10000009, 10000008, 10000011, 100000104

 

Anyone know a way to do this?

 

Thanks in advance!

Need Help
  • az38's avatar
    az38
    6 years ago

    Hi Anonymous 

    create a new column

    Number of candidates = PATHLENGTH(SUBSTITUTE('Table'[Candidates], ",", "|"))

4 Replies

  • AlB's avatar
    AlB
    Community Champion
    6 years ago

    Hi Anonymous 

    I guess you could create a calcualted column that counts the number of commas (and add 1), since that seems to be the separator:

    Number of candidates =
LEN ( Table1[Candidates] ) - LEN ( SUBSTITUTE ( Table1[Candidates], ",", "" ) ) + 1

     

    Please mark the question solved when done and consider giving kudos if posts are helpful.

    Contact me privately for support with any larger-scale BI needs

    Cheers 

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi AlB I am going with the solution of az38 because your calcuation also returns a "1" when the candidate field is empty and the other solution doesn't. Would eb a good solution if everythign is fileld in tho, but I didn't provide u the information that there were missing values.

  • az38's avatar
    az38
    Community Champion
    6 years ago

    Hi Anonymous 

    create a new column

    Number of candidates = PATHLENGTH(SUBSTITUTE('Table'[Candidates], ",", "|"))
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi thanks for your quick answer, this worked like a charm!