+ Reply to Thread
Results 1 to 4 of 4

DAX formula to count number of times specific word occur in a column in a pivot table

  1. #1
    Registered User
    Join Date
    10-29-2015
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    5

    DAX formula to count number of times specific word occur in a column in a pivot table

    Hello all,

    Need some help on this

    In my excel table (call CallTABLE), I have a column with header named Site with the following four cities: Portland, Washington, Houston, Pittsburgh)

    I have another column with header named "Result", and within the column it will have either Met, Not Met, or Not Evaluate.

    I have my pivot table setup as data-model type pivot table as I need it for other items.

    Is there a DAX formula which count how many times Met, Not Met, and Not Evaluate appears for each Site?


    Thanks in advance.

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,423

    Re: DAX formula to count number of times specific word occur in a column in a pivot table

    It would seem that you could put Site and Result in the Rows field and then Result in the Values field (summarized by Count).
    If you need a DAX formula have you tried adding the following explicit measure to the data model:=COUNTROWS(CallTABLE)
    It may be more helpful if you utilize the instructions in the banner at the top of the page to upload a sample.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  3. #3
    Registered User
    Join Date
    10-29-2015
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    5

    Re: DAX formula to count number of times specific word occur in a column in a pivot table

    Thanks JeteMc

    I do have another question. Hope you can help. Here is a sample of what my table (CallTable) looks like

    Month AgentName Result
    November Agent1 Met
    November Agent2 Met
    November Agent3 Met
    November Agent4 Met
    November Agent5 NotMet
    November Agent6 NotMet
    November Agent7 Met
    November Agent8 Met
    November Agent9 NotEvaluate
    November Agent10 Met
    November Agent11 NotEvaluate
    November Agent12 NotEvaluate
    December Agent13 Met
    December Agent14 Met
    December Agent15 Met
    December Agent16 Met
    December Agent17 Met
    December Agent18 NotEvaluate
    December Agent19 Met
    December Agent20 NotEvaluate
    December Agent21 NotEvaluate
    December Agent22 NotEvaluate
    December Agent23 NotMet
    December Agent24 Met


    I am looking for DAX formula where I can find out what the % of Met, NotMet, NotEvaluate, is for each month.

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,423

    Re: DAX formula to count number of times specific word occur in a column in a pivot table

    The same explicit measure:=COUNTROWS(TableName)
    On sheet 1 is the table produced from the data in post #3.
    On sheet 2 is the pivot table produced from the data model. The field setting for the Count column is set to % of Parent Total (Base field = Month)
    On sheet 3 is a pivot table produced directly from the table on sheet 1. Again the field setting for the Count of Result column is set to % of Parent Total (Base field = Month)
    In the future please utilize the instructions in the banner at the top of the page to upload a sample as opposed to placing data in a post that will have to be copied and edited before it can be manipulated/tested.
    Let us know if you have any questions.
    Attached Files Attached Files

+ 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. [SOLVED] Count all the words in a string before a specific word occur
    By PaulM100 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-22-2019, 04:28 AM
  2. Count number of times a specific word appears
    By csepesy in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-07-2017, 09:48 PM
  3. Count the number of times a quantity of entries occur within a range
    By sameg in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-27-2015, 10:35 AM
  4. program a formula to count number of times event occur before a specific time.
    By yinxzon in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-17-2014, 04:15 AM
  5. Replies: 8
    Last Post: 07-14-2013, 07:29 PM
  6. [SOLVED] Formula to count number of times specific word used in column range
    By flammer4 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-07-2012, 01:42 PM
  7. count the number of times a specific word appears in a column
    By BAR in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-27-2006, 12:10 PM

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