Forum Discussion

steambucky's avatar
steambucky
Helper III
3 years ago
Solved

Calculating percentages from 2 different spreadsheets

I want to create a pie chart visual, that shows how many of my workers attended a course VS how many there are.  

 

This is a simulation of my issue. I have 2 separate different spreadsheets.  The workers: 

 

Workers
[email protected]
[email protected]
[email protected]
[email protected]

 

And attendees of the course: 

Course attendees
[email protected]

 

The pie chart would look like 1/4 of my staff attended VS 3/4 did not attend.  Whats the best way to do this?  I THINK that the best way to do it would be of some sort of custom column BUT normally it's only looking at 1 datasource. 

 

Any ideas/directions? 

 

Thanks in advance. 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi steambucky ,

     

    You can refer to the following steps:

    (1) My test data is the same as yours.

    (2) We can click "New Measure" to create two measures:

    Attend = COUNTROWS( DISTINCT('Course'))
    Workers = COUNTROWS( DISTINCT('Workers'))

    (3) Then we can realize your needs:

     

    Best Regards,

    Neeko Tang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    steambucky , Create two measures

     

    Distinctcount(Workers[Email])

     

    calculate(Distinctcount(Workers[Email]) , filter( 'Workers', Workers[Email] in values('Course attendees'[Email]) ))

     

    Use both these measures in pie, no legend. Also change table and column name as per need

  • steambucky's avatar
    steambucky
    Helper III
    3 years ago

    amitchandak  Thank you for your response.

     

    I have tried to implement your suggestion but I am creating some sort of error. I am now going to swap to real names of spreadsheets and columns and use the real example. I am trying to find out the percentage of our conference attendees that did not take part in our survey.  

     

    Email addresses of the staff that have attended our conference:

     

    'SICON 2022 attendance'[Email SICON]
     
     
    Email addresses of those that completed the survey: 

     

    'SICON  Survey'[Email Survey]

     

    Based on your email I created two measures: 

     

    all_staff = DISTINCTCOUNT(('SICON 2022 attendance'[Email SICON]))   

     

    And

     

    MISSING SURVEYS = CALCULATE(DISTINCTCOUNT('SICON 2022 attendance'[Email SICON]),FILTER('SICON 2022 attendance'[Email SICON]in VALUES('SICON  Survey'[Email Survey])))

     

    I get this message: 

     

    Too few arguments were passed to the FILTER function. The minimum argument count for the function is 2.

     

    Screenshot:    

     

    I think I am close but doing something wrong. Can you assist? Thanks in advance for any advice you can supply. 

     

     

     

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi steambucky ,

     

    You can refer to the following steps:

    (1) My test data is the same as yours.

    (2) We can click "New Measure" to create two measures:

    Attend = COUNTROWS( DISTINCT('Course'))
    Workers = COUNTROWS( DISTINCT('Workers'))

    (3) Then we can realize your needs:

     

    Best Regards,

    Neeko Tang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.