Calculating percentage of users accessing the tool based on user access list

[rachelb123]

Hi, 

 I have tried to post this multiple times and it keeps getting removed not sure why, but I am creating my first real dashboard and I am trying to calculate the rate of unique users using a specific tool. I have two tables, one with usage data and another with user access data. The original way I calculated it only had one tool so it was pretty easy to just seperate out the users and pull their names from both tables and just compare but I'm not sure how to proceed when there is a dataflow with multiple tools in one table. 

Product: Tool A = Adoption Rate of 1/3 Tool B = Adoption Rate of 2/3 and Tool C = Adoption Rate of 2/2

Email	Name	Role	Group
abc@gmail.com	John Smith	Procurement Specialist	car
bcd@gmail.com	Smith John	Procurement Specialist	health
efg@gmail.com	Jane Doe	Procurement Specialist	health
hij@gmail.com	Doe Jane	Procurement Specialist	car
klm@gmail.com	Peter Spiet	Procurement Specialist	medicine
nop@gmail.com	Spiet Peter	Procurement Specialist	medicine
qrs@gmail.com	Don Jan	Procurement Specialist	health
tuv@gmail.com	Jan Don	Procurement Specialist	car

ToolName	UserName	UserEmail	UseDate
A	Doe Jane	n	1-Jul-25
A	Doe Jane	n	2-Jul-25
B	Smith John	n	1-Jul-25
C	Doe Jane	n	1-Jul-25
B	Smith John	n	2-Jul-25
A	Doe Jane	n	3-Jul-25
C	Peter Spiet	n	4-Jul-25
C	Spiet Peter	n	4-Jul-25
B	Don Jan	n	1-Jul-25

[kushanNa]

Hi @rachelb123 

I'm not entirely sure what is the purpose of the first table , but try out this measure and see if this is the result you are looking for ? 

Adoption Rate =
DIVIDE (
    DISTINCTCOUNT ( UsageTable[UserName] ),   -- unique users for this tool
    COUNTROWS ( UsageTable )                  -- total usage rows for this tool
)

[FBergamaschi]

Can you show an example of the result you are looking for?

[rachelb123]

Tool	Adoption Rate
A	0.33
B	0.67
C	1.0

[v-kpoloju-msft]

Hi @rachelb123,

Thank you for reaching out to the Microsoft fabric community forum. I reproduced the scenario, and it worked on my end. I used my sample data and successfully implemented it.

I am also including .pbix file for your better understanding, please have a look into it:

Hope this helps clarify things and let me know what you find after giving these steps a try happy to help you investigate this further.

Thank you for using the Microsoft Fabric Community Forum.

[rachelb123]

Hi @v-kpoloju-msft , 

  I tried your method and It does not work, I believe this is my fault for oversimplyfing my example tables and not showing the nuance behind it. I also see that when an email is not provided then it stops the counting but in my case there is always email information and it will never be null as that is the way we pull user name, group information. I apologise for that oversight. In my real data Tool C is used by all groups (Car, Medicine and Health) I created this table to provide a more realistic representation of my data. 

User Access List

Email	Name	Role	Tool
abc@gmail.com	John Smith	Procurement Specialist	A
bcd@gmail.com	Smith John	Procurement Specialist	B
efg@gmail.com	Jane Doe	Procurement Specialist	B
hij@gmail.com	Doe Jane	Procurement Specialist	A
klm@gmail.com	Peter Spiet	Procurement Specialist	C
nop@gmail.com	Spiet Peter	Procurement Specialist	C
qrs@gmail.com	Don Jan	Procurement Specialist	B
tuv@gmail.com	Jan Don	Procurement Specialist	A
abc@gmail.com	John Smith	Procurement Specialist	C
bcd@gmail.com	Smith John	Procurement Specialist	C
efg@gmail.com	Jane Doe	Procurement Specialist	C

 This is more realistic to the data I have. I went in and changed all the grouping in my table to make it easier for me to calculate so now Tool is present next to each user but I did have to duplicate the user names as some users have access to multiple tools. Please let me know if this is not a good form of practice, I'm new to Power BI so im not really sure about these things.  So the new output with the above user access table would be 

Output

Tool	Adoption Rate
A	1/3 - 33%
B	2/3 - 66%
C	2/5 - 40%

To explain C's answer - C has a total of 5 users with access ( out of these 5 there are some who have access to multiple tools hence the duplicity in their username and email) In the usage date table you will notice Doe Jane as a user of Tool C but she is not an applicable user in this table hence why she is not counted towards the total or the unique user number so there are only 2 applicable users and 5 users with access. I hope this makes sense, apologies again and appreciate all the help! 

[v-kpoloju-msft · Accepted solution]

Hi @rachelb123,

I reproduced the scenario again, and it worked on my end. I used my sample data and successfully implemented it.

I am also including .pbix file for your better understanding, please have a look into it:

Hope this helps clarify things and let me know what you find after giving these steps a try happy to help you investigate this further.

Thank you for using the Microsoft Fabric Community Forum.

[rachelb123]

Thank you!! I am realising now why I had issues with this, I forgot to relate the table (rookie mistake) hence why I was getting values over 100%. This really helped me learn about how to remove exceptions and duplicates. Thank you again!