Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I’d like to create a measure to calculate the proportion of people by ethnicity group and job group. I have created a measure to calculate the proportion of people by ethnicity but need some help to do it by ethnicity group, i.e. the total number of people in an ethnicity group over the distinct number of people within an ethnicity group.
In the dataset I am using, people can select up to three individual ethnicities and then these are grouped into broader ethnicity groups for reporting. The Ethnicity ID columns join the fact table and the dim table and is a many to one relationship, respectively. (The fact table also includes job group in case it’ll affect the measure as I include it on the chart which will be the proportion of people by ethnicity group and job group)
Fact table:
Person ID | Ethnicity ID | Job Group |
1 | 9 | 1 |
1 | 8 | 1 |
1 | 7 | 1 |
2 | 9 | 1 |
2 | 8 | 1 |
3 | 5 | 1 |
4 | 9 | 2 |
5 | 8 | 3 |
5 | 7 | 3 |
6 | 9 | 2 |
6 | 5 | 2 |
6 | 3 | 2 |
Dim table:
Ethnicity ID | Ethnicity | Ethnicity Group |
9 | English | European |
8 | Scottish | European |
7 | Welsh | European |
5 | French | European |
3 | Egyptian | African |
In Person ID 2’s case, they have selected English, Scottish, and Welsh, and their broader ethnicity group for both of these is European. My measure is currently calculating this as 3 divided by 1. I’d like to update it to be 1 divided by 1 because even though they have selected three ethnicities, they all fall under the same ethnicity group and they’re the same person. The denominator is correct at 1 as it is counting just the one person. In Person ID 6’s case for example, they have selected English, French, and Egyptian. I’d like them to be counted once under the ethnicity group European, and also once under African.
Regards
Solved! Go to Solution.
Hi , try to create a new table with the code below:Table =
I tried and it works.
@FreemanZ , thanks for your quick response.
Using the dataset above, the data would look like this:
Job group | European | African | Distinct no. people | Percentage |
1 | 3 | 3 | 100% | |
2 | 2 | 1 | 2 | 150% |
3 | 1 | 1 | 100% |
Hi , try to create a new table with the code below:Table =
I tried and it works.
@Pamela1 ,You may also do it step by step, by creating 4 measures and putting them into a table visual, like this:
Decomposed code for the 4 measures:
how will your expected result look like?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
22 | |
12 | |
8 |
User | Count |
---|---|
77 | |
50 | |
46 | |
16 | |
12 |