How to calculate the proportion of a staff

[PBI_newuser]

Hi,

How to get the proportion of a staff with the below raw data.

Expected Output:

Staff	Proportion
Alan	1
Amelia	1/9
Amy	1
Ben	1/4
Bernard	1/5
Bob	1/4
Clark	1/8

Raw Data:

Staff	Product
Amy	SYS
Alan	SYS
Amelia	BT
Amelia	D
Amelia	EPC
Amelia	MDI
Amelia	GIT
Amelia	PHX
Amelia	AP
Amelia	PHM
Amelia	VRT
Ben	FP
Ben	PS
Ben	TMP
Ben	TSP
Bob	FP
Bob	PS
Bob	TMP
Bob	TSP
Bernard	IO
Bernard	SYS
Bernard	TLA
Bernard	UCA
Bernard	WCA
Clark	BT
Clark	F
Clark	EPC
Clark	MDI
Clark	AP
Clark	AP
Clark	PHM
Clark	VRT

[PBI_newuser]

Hi @amitchandak , thanks! after getting the contribution of staff, how can I get the total contribution per product?

Expected Output:

Product	Contribution
AP	1/9+1/8
BT	1/9+1/8
D	1/9
EPC	1/9+1/8
F	1/8
GIT	1/9
IO	1/5
MDI	1/9+1/8
PHM	1/9+1/8
PHX	1/9
PS	1/4+1/4
SYS	1+1+1/5
TLA	1/5
TMP	1/4+1/4
TSP	1/4+1/4
UCA	1/5
VRT	1/9+1/8
WCA	1/5

[amitchandak]

@PBI_newuser , Try count below

divide(distinctcount(Table[Staff]),count(Table[Product]))

[PBI_newuser]

Hi @amitchandak, I would like to calculate the total contribution per product as shown in the screenshot below. Sample here.

[amitchandak]

@PBI_newuser , Please find the attached pbix and see if that can help 

[PBI_newuser]

Hi @amitchandak , I understand the measure you created. But I would like to create another measure based on the Ratio you have created.

Ratio = DIVIDE(DISTINCTCOUNT('Table'[Staff]), COUNT('Table'[Product]))

For example, Amelia and Clark are under Product AP.

Amelia's ratio is 0.11 while Clark's ratio is  0.13. 

The total ratio for Product AP = 0.11 + 0.13 = 0.24.

How to calculate the total ratio for each product?

[amitchandak]

@PBI_newuser , Try like

sumx(values('Table'[Staff]),DIVIDE(DISTINCTCOUNT('Table'[Staff]), COUNT('Table'[Product])))

[PBI_newuser]

Hi @amitchandak , thanks for your help! But I couldn't get the expected result. 

[amitchandak · Accepted solution]

@PBI_newuser , Please find the file after signature

Staff Contribution = SUMX(VALUES('Table'[Staff]),CALCULATE(divide(distinctcount('Table'[Staff]),distinctcount('Table'[Product])),ALLEXCEPT('Table','Table'[Staff])))

[PBI_newuser]

Hi @amitchandak , it works!! Thank you so much! 😊

[amitchandak]

@PBI_newuser , Create a new measure like

divide(distinctcount(Table[Staff]),distinctcount(Table[Product]))