Forum Discussion
How to divide between two tables?
Hi
I am having below three tables,
| Main table | ||
| School Name | Number of Students | ID NO |
| School 1 | 100 | 1 |
| School 2 | 200 | 2 |
| School 3 | 50 | 3 |
| School 4 | 102 | 4 |
| School 5 | 180 | 5 |
| School 6 | 900 | 6 |
| School 7 | 50 | 7 |
| School 8 | 102 | 8 |
| School 9 | 180 | 9 |
| School 2 - Table | ||
| School Name | Name of student | ID NO |
| School 2 | Student 1 | 2 |
| School 2 | Student 2 | 2 |
| School 2 | Student 3 | 2 |
| School 2 | Student 4 | 2 |
| School 2 | Student 5 | 2 |
| School 2 | Student 6 | 2 |
| School 9 - Table | ||
| School Name | Name of student | ID NO |
| School 9 | Student 1 | 9 |
| School 9 | Student 2 | 9 |
| School 9 | Student 3 | 9 |
| School 9 | Student 4 | 9 |
| School 9 | Student 5 | 9 |
| School 9 | Student 6 | 9 |
I have connected School 2 and School 9 table with the main table through ID NO. It's many to one relationship
I want to count the number of records in School 2 and School 9 and divide the same with the number of students in the main table. For example, School 2 has 6 records and it has 200 students in the main table. I want to calculate the percentage, so 6/200 * 100
I want the table as below
| School Name | Number of Students | Total School 2 records | % of students |
| School 2 | 200 | 6 | 3% |
| School 9 | 180 | 6 | 3% |
Can you please advise how to do it? Do we need to write only Dax measures? or we can find the percentage without dax measures
bourne2000
Try the following 2 measures, might give you some insights:School 2 = COUNTROWS('School 2')/CALCULATE(SUM('Main Table'[Number of Students]),FILTER('School 2',[ID NO] in VALUES('Main Table'[ID NO])))
School 9 = COUNTROWS('School 9')/CALCULATE(SUM('Main Table'[Number of Students]),FILTER('School 9',[ID NO] in VALUES('Main Table'[ID NO])))Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.
1 Reply
bourne2000
Try the following 2 measures, might give you some insights:School 2 = COUNTROWS('School 2')/CALCULATE(SUM('Main Table'[Number of Students]),FILTER('School 2',[ID NO] in VALUES('Main Table'[ID NO])))
School 9 = COUNTROWS('School 9')/CALCULATE(SUM('Main Table'[Number of Students]),FILTER('School 9',[ID NO] in VALUES('Main Table'[ID NO])))Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.