英文:
Is there a way I can filter values of two tables in PowerBI and then do aggregation such as getting the percentages/ratio
问题
你可以尝试使用以下公式来计算每个chiefdom的未访问学校的百分比：
Percentage Not Visited =
DIVIDE (
COUNTROWS ( Table2 ) - COUNTROWS ( Table1 ),
COUNTROWS ( Table2 )
)
这个公式将计算未访问学校的数量，然后除以总学校的数量，得到未访问学校的百分比。
英文:
I am working on PowerBI and I have two tables like this:
Table-1: This table shows the number of schools visited in a certain month
| School | Chiefdom | District | School Type |
|---|---|---|---|
| S-1 | Ch-1 | D-1 | ST-1 |
| S-2 | Ch-2 | D-1 | ST-1 |
| S-3 | Ch-2 | D-1 | ST-1 |
| S-4 | Ch-3 | D-2 | ST-1 |
| S-5 | Ch-2 | D-1 | ST-1 |
| S-6 | Ch-1 | D-1 | ST-1 |
Table-2: This table shows the total number of schools
| School | Chiefdom | District | School Type |
|---|---|---|---|
| S-1 | Ch-1 | D-1 | ST-1 |
| S-2 | Ch-2 | D-1 | ST-1 |
| S-3 | Ch-2 | D-1 | ST-1 |
| S-4 | Ch-3 | D-2 | ST-1 |
| S-5 | Ch-2 | D-1 | ST-1 |
| S-6 | Ch-1 | D-1 | ST-1 |
| S-7 | Ch-1 | D-1 | ST-1 |
| S-8 | Ch-2 | D-1 | ST-1 |
| S-9 | Ch-4 | D-2 | ST-1 |
| S-10 | Ch-4 | D-2 | ST-1 |
| S-11 | Ch-5 | D-2 | ST-1 |
| S-12 | Ch-3 | D-2 | ST-1 |
My goal: I ought to get a percentage of schools not visited per chiefdom from the above two tables. The idea is to filter by each chiefdom in both tables, then subtract each filtered chiefdom in table 1 from the same in table 2, and get percentage of schools not visited. I tried something like this but the results do not add up. How do you think I should tackle this?
Measure =
CALCULATE (
1 - COUNT(Table1[chiefdom]) / COUNT(Table2[chiefdom])
)
答案1
得分: 1
FILTER() 返回一个表对象，所以请使用 COUNTROWS(FILTER()) 而不是 COUNT(FILTER())。
英文:
FILTER() returns a table object, so use COUNTROWS(FILTER()) instead of COUNT(FILTER())
答案2
得分: 0
尝试以下度量：
未访问学校的百分比 =
计算 (
1 - 计数 (筛选 (表1，表1[首领区] = 表2[首领区])) / 计数 (筛选 (表2，表1[首领区] = 表2[首领区]))
)
结果：
未访问学校的百分比 = 1 - 3 / 6 = 0.5 = 50%
这意味着首领区内有50%的学校未被访问。
英文:
Try the below Measure:
Percentage of schools not visited =
CALCULATE (
1 - COUNT(FILTER(Table1, Table1[chiefdom] = Table2[chiefdom])) / COUNT(FILTER(Table2, Table1[chiefdom] = Table2[chiefdom]))
)
Result:
Percentage of schools not visited = 1 - 3 / 6 = 0.5 = 50%
This means that 50% of the schools in the chiefdom are not visited.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论