Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Count Statuses - DAX Help!!!!

I am currently using an "IF" function to apply a value of "Applied" or "N/A" within a column of a table.

 

I am looking for some help in a DAX statement that will allow me to do the following:

  • Count the number of "Applied"
  • Count the number of "N/A"
  • Divide each of the above by the total to determine the percenatge of each

 

Thanks in advance.

Need Help
Show and Tell
Tips and Tricks
  • jthomson's avatar
    jthomson
    8 years ago

    Something like:

     

    Total Applied = calculate(countrows(your table),your table[your column]="Applied")

     

    Do similar for N/A and the percentage of these should be trivial from there

10 Replies

  • jthomson's avatar
    jthomson
    Solution Sage
    8 years ago

    Something like:

     

    Total Applied = calculate(countrows(your table),your table[your column]="Applied")

     

    Do similar for N/A and the percentage of these should be trivial from there

  • miltenburger's avatar
    miltenburger
    Helper V
    8 years ago

    You will need a few Dax measures to do it

    First of all:

     

    totalRows = CALCULATE(COUNTROWS(yourtable; yourcolumnname = "Applied"; yourcolumnname = "N/A")

     Then count for "Applied" or count "N/A"

    totalApplied = CALCULATE(COUNTROWS(yourtable);yourcolumnname = "Applied")
    totalNA = CALCULATE(COUNTROWS(yourtable);yourcolumnname = "N/A")

    Percentage

    %Applied = (totalApplied / totalRows) * 100
%NA = (totalNA / totalRows) *  100

    Hope this helps you out?

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      miltenburger

       

      I think the use of ";" is throwing a wrench into the application of the DAX statements.

       

      Any thoughts?

      • miltenburger's avatar
        miltenburger
        Helper V
        8 years ago

        Hi Anonymous

         

        Jep, my system needs a " ; "

        Your system possibly needs a " , " 

  • SivaMani's avatar
    SivaMani
    Resident Rockstar
    8 years ago

    Anonymous,

     

    Create a measure like below,

     

    % =
    var __a = CALCULATE(COUNTROWS(Table1),Table1[Status] = "A" )
    var __b = CALCULATE(COUNTROWS(Table1),Table1[Status] = "B" )
    Return
    DIVIDE(__b,__a)

     

    Then change the format as Percentage located under modeling Tab

     

    Regards ,

    Siva