Forum Discussion

Mike35's avatar
Mike35
Frequent Visitor
1 year ago
Solved

Calculating a percentage between two tables

Hi all,

 

Trying to calculate a percentage between two tables with the below coding, and struggling to get an ouput. Wondered if anyone can point me in the direction of where I am going wrong?

 

I'm looking to generate a completion percentage per area, of staff completing a form. In essence two tables, first table ('Quiz Results') is the output of the form whereby staff are selecting the area they are based in during the form completion [Area]. Second table ('Staff Base') is a staff list with a field for the location the staff are based in [Base_Name].

 

AreaCompletion% = 
DIVIDE(
    CALCULATE(
        COUNTROWS('Quiz Results'),
        'Quiz Results'[Area] = "London"
    ),
    CALCULATE(
        COUNTROWS('Staff Base'),
        'Staff Base'[Base_Name] = "London"
    )
)

 

When applying to a card I'm just getting a blank output. Expected output would be 56.48%, as we have 431 responses with an area of "London" where there are 763 staff with a Base_Name of "London".

 

Thanks

  • Mike35 Measure for the count of quiz results per area:

    QuizResultsCount = COUNTROWS('Quiz Results')

     

    Measure for the count of staff per base:

    StaffCount = COUNTROWS('Staff Base')

     

    Measure to calculate the completion percentage:

    DAX
    AreaCompletion% =
    DIVIDE(
    [QuizResultsCount],
    [StaffCount]
    )

2 Replies

  • Mike35 Measure for the count of quiz results per area:

    QuizResultsCount = COUNTROWS('Quiz Results')

     

    Measure for the count of staff per base:

    StaffCount = COUNTROWS('Staff Base')

     

    Measure to calculate the completion percentage:

    DAX
    AreaCompletion% =
    DIVIDE(
    [QuizResultsCount],
    [StaffCount]
    )