+ Reply to Thread
Results 1 to 9 of 9

Powerpivot formula to show value that appears most in a column

  1. #1
    Registered User
    Join Date
    01-13-2015
    Location
    Leicester, England
    MS-Off Ver
    2013
    Posts
    42

    Powerpivot formula to show value that appears most in a column

    Hello, this is my first ever post so bear with me please . I have a power pivot table and I'm struggling to find a formula that would return the value that appears most often in a column. My table(called "Table") has a similar format to the one in the attached image but quite larger and with more columns. I chose a power pivot as I needed a few calculated fields that are more complex.

    Capture.JPG

    In this example, I would need a formula that would return the value "Astronaut" (will always be a text/general value) because this is the value that appears most often in the Dreamjob column. I was thinking that using Maxx and Distinct(I understand this returns a column of values) might work but I cannot find the right syntax. I would prefer to do this without adding another column to the table as the table is huge as it is and is updated regularly (a few times a day). Is there any way to do this?

    Thanks
    Alex

  2. #2
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Powerpivot formula to show value that appears most in a column

    It would have to be a calculated column in the table since a measure won't return a text value. I'm not really sure Power Pivot is best suited for this - what do you actually plan to do with this value?
    Remember what the dormouse said
    Feed your head

  3. #3
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Powerpivot formula to show value that appears most in a column

    This array formula will return text value. However, I can't tell how it is going to perform over large data set.

    in any cell

    =INDEX(B2:B9,MODE(IF(ISTEXT(B2:B9),MATCH(B2:B9,B2:B9),0)))

    ***Array formula must be entered by using key combination of CTRL+SHIFT+ENTER and not just ENTER
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  4. #4
    Registered User
    Join Date
    01-13-2015
    Location
    Leicester, England
    MS-Off Ver
    2013
    Posts
    42

    Re: Powerpivot formula to show value that appears most in a column

    Hi AlKey,

    I need it as a calculated field in my power pivot not to add in my sheet

  5. #5
    Registered User
    Join Date
    01-13-2015
    Location
    Leicester, England
    MS-Off Ver
    2013
    Posts
    42

    Re: Powerpivot formula to show value that appears most in a column

    Hi Rory,

    I can have it return my text value if I add a column in my table counting how many times each value appears (I'd name the column "Count"). I can then use this to return the value: LOOKUPVALUE(Table[Dreamjob],Table[Count],MAX(Table[Count])). What I want is to avoid adding this column.

  6. #6
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Powerpivot formula to show value that appears most in a column

    Where do you expect to calculate it? You can't use a measure, as I mentioned, but you still haven't said what you want to do with the value.

  7. #7
    Registered User
    Join Date
    01-13-2015
    Location
    Leicester, England
    MS-Off Ver
    2013
    Posts
    42

    Re: Powerpivot formula to show value that appears most in a column

    I simply need to know the value. I expect to do nothing with it except know which is the most common dreamjob in my list.

    Like I said, this is a mock table. In reality, my company needs to know what is the main cause for cancellation of services so measures can be then taken. Hope this answers your question.
    Last edited by AlexandraT; 03-13-2015 at 11:14 AM.

  8. #8
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Powerpivot formula to show value that appears most in a column

    In that case I can't see a need for it to be in Power Pivot at all. Why don't you just calculate it as AlKey suggested from the raw data?

  9. #9
    Registered User
    Join Date
    01-13-2015
    Location
    Leicester, England
    MS-Off Ver
    2013
    Posts
    42

    Re: Powerpivot formula to show value that appears most in a column

    OK. Thank you

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. PowerPivot - Show Actual OR Budget
    By Radovan in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 09-04-2014, 10:44 AM
  2. Replies: 2
    Last Post: 06-02-2014, 09:34 AM
  3. Show last 5 weeks in powerpivot
    By aftabn10 in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 01-23-2014, 10:51 AM
  4. Replies: 0
    Last Post: 11-20-2012, 05:34 AM
  5. [SOLVED] only show/highlight rows where a certain word appears in any column
    By intothewild in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 03-28-2012, 03:26 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1