Forum Discussion

Seliebl's avatar
Seliebl
New Member
6 years ago

Calculating % Compete with Conditions

Good day, I'm trying to create a new measure that shows the % percent of effective documents out of the total number of documents for a visual in Power BI. These measures seem to be off and I'm not sure what I'm missing. 

 

First, I wanted to select a condition that counted all the items in the list "GOP & S-GOP Traceability", but only filtered on the "Document Status" field where items are tagged as "Effective". Then, I wanted to divide to the total number of "Effective" documents by the Total Number of Documents to get a percentage. 

 

Written out: Total # of Effective Documents/Total # of Documents = % Completed

 

Below is my attempt to write these formulas:

 

  1. Total Completed = CALCULATE(COUNTA('GOP & S-GOP Traceability'[Document Status],FILTER('GOP & S-GOP Traceability'[Document Status]="Effective"
  2. Total = COUNTA('GOP & S-GOP Traceability'[Document Status])
  3. % Completed = [Total Completed] / [Total]

 

I'm using COUNT A because these fields do not contain numbers, but words.

Any help is greatly appreciated. Thanks!

2 Replies

  • Nathaniel_C's avatar
    Nathaniel_C
    Community Champion
    6 years ago

    Hi Seliebl ,
    Starting with a table called Trace, try this.

    Pct effective =
VAR _num =
    CALCULATE ( COUNTROWS ( Trace ), Trace[Document Status] = "Effective" )
VAR _all =
    COUNTROWS ( ALL ( Trace ) )
RETURN
    DIVIDE ( _num, _all )

      

     



    Let me know if you have any questions.

    If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
    Nathaniel

     

  • pmlathrop8cc's avatar
    pmlathrop8cc
    New Member
    6 years ago

    You can try this as well to count total # of documents with effective status over total # of documents.

    Eff% = CALCULATE(COUNTA(TableName[Document Status]), TableName[Document Status]="Effective")/ calculate(COUNTA(TableName[Document Status]))