Calculate the proportion of people over a distinct count by category

[Pamela1]

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

[Pamela1]

@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%

[FreemanZ · Accepted solution]

Hi , try to create a new table with the code below:Table =

VAR Table1 =

ADDCOLUMNS(

     VALUES(Fact[Job Group]),

    "European",

    CALCULATE(

        DISTINCTCOUNT(Fact[Person ID]),

        DIM[Ethnicity Group]="European"

     ),

    "African",

    CALCULATE(

        DISTINCTCOUNT(Fact[Person ID]),

        DIM[Ethnicity Group]="African"

     ),

     "People",

     CALCULATE(

        DISTINCTCOUNT(Fact[Person ID])

     )

)

VAR Table2=

ADDCOLUMNS(

    Table1,

    "Percentage",

    FORMAT(

        DIVIDE([European]+[African], [People]),

        "00%"

     )

)

RETURN

    Table2

I tried and it works.

@Pamela1

[FreemanZ]

@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:

European :=

    CALCULATE(

        DISTINCTCOUNT(Fact[Person ID]),

        DIM[Ethnicity Group]="European"

     )

 

African :=

    CALCULATE(

        DISTINCTCOUNT(Fact[Person ID]),

        DIM[Ethnicity Group]="African"

     )

 

People :=

     CALCULATE(

        DISTINCTCOUNT(Fact[Person ID])

     )

 

Percentage :=

    FORMAT(

        DIVIDE([European]+[African], [People]),

        "00%"

     )

[FreemanZ]

how will your expected result look like?