Forum Discussion
need help!
Hello, I have 2 source tables. Table 1 is a comprehensive list of companies (each record is a company), and Table 2 is a list of individuals, their companies, and survey results for that individual (each record is an individual). I've linked the two tables with a company ID, and in a matrix I'm able to show aggregrated survey results for each company that has participated (some companies in Table 1 have not participated and are not found in Table 2). In this matrix, I'm also showing companies that have not participated, by selecting 'Show items with no data'. My goal is to show the % of companies that have participated, either a measure in a card or another visual showing % participated vs. % not. Example:
| Company | # Completions |
| A | |
| B | 3 |
| C | |
| D | 4 |
| E | |
| F | 2 |
| G | 3 |
The metric should result as 57%, 4 out of 7.
Please help! I can't figure this out!
Anonymous ,
Suggest you create a new Calculated Column in Table 2 (your Survey table):
KeyCompany = RELATED( DIM_Company[CompanyID] )My table DIM_Company is your Table 1 (Master Key Companies)
If the Company ID is in that table, it will appear, else will appear as Blank.
Then do a DISTINCTCount on this new column. This will exclude unwanted companies.
KeyCompanies = Calculate( DISTINCTCOUNT( Surveys[KeyCompany] ), FILTER( Surveys, Surveys[KeyCompany] <> BLANK() ))Hope this works for you.
Regards,
5 Replies
Anonymous ,
Use the DISTINCTCOUNT function. I will demonstrate using 3 Measures:
1. Calculate the unique number of Companies in your dataset:
Number of Companies = DISTINCTCOUNT( Table1[CompanyID]) -- Using COUNTROWS instead should also give you the same result.2. Determine number of Companies that responded:
Number Companies Responded = DISTINCTCOUNT( Table2[CompanyID])3. Then percentage responded:
%Response = DIVIDE( [Number Companies Responded], [Number of Companies], 0 )If you want, you can combine all of this into 1 Measure...I just split it so you can more easily see the method and know if your Measures are calculating accurately.
Hope this gets you going.
Regards,
Hi, thanks!! I'm making progres, but Table 2 (survey results) contains companies that are not part of the key companies list in Table 1 (comprehensive list of key companies).
So the DISTINCTCOUNT measures are giving me full counts, which is great for Table 1 but for Table 2 the numerator needs to be only companies that are also found in Table 1. Thanks again, this is hugely appreciated!!
So, is Table 1 your master list of companies? Meaning, any company in table 2 has to be in table 1. But not every company in table 1 appears in table 2, correct?
Anonymous ,
Suggest you create a new Calculated Column in Table 2 (your Survey table):
KeyCompany = RELATED( DIM_Company[CompanyID] )My table DIM_Company is your Table 1 (Master Key Companies)
If the Company ID is in that table, it will appear, else will appear as Blank.
Then do a DISTINCTCount on this new column. This will exclude unwanted companies.
KeyCompanies = Calculate( DISTINCTCOUNT( Surveys[KeyCompany] ), FILTER( Surveys, Surveys[KeyCompany] <> BLANK() ))Hope this works for you.
Regards,
