cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
luc Frequent Visitor
Frequent Visitor

Show value of one table as percentage of another table value

I have an operations table (date, operation, userid columns) and a users table(userid, name columns), with userId in operation referencing userId in user (many to one) and I want to know the percentage of users who performed an operation on each day.

 

My problem is, when creating a line graph visual with operation.date as axis and operation.userId distinct count as value and showing the value as percentage, the visual says 35% of the users performed an operation in a day, even though only 50 users out of 3000 did some operation that day.

 

I believe powerBI is calculating the percentage based on the number of different userIds that show up on the operation table, rather than the number of different userIds in the Users table.

 

So how do I calculate the percentage based on the total number of users in the User table?

2 ACCEPTED SOLUTIONS

Accepted Solutions
luc Frequent Visitor
Frequent Visitor

Re: Show value of one table as porcentage of another table value

DIVIDE(DISTINCTCOUNT(Operations[Operation.UserId]), DISTINCTCOUNT(Users[User.UserId])) does result in a percentage but in the 0,09 format rather than 9%, which is somewhat acceptable but not ideal. I could multiply by 100 but how do I add the % sign?

 

I have no idea why but telling it to show as percentage shows exacly the same percentages I get when using operation.UserId which is really weird.

 

View solution in original post

robenanderson Frequent Visitor
Frequent Visitor

Re: Show value of one table as porcentage of another table value

if you select the field and go to the modeling tab in the top ribbon, you should be able to change the format of the numbers to a percent. That shouldn't change the numbers at all, so if that's happening, that's really interesting.

View solution in original post

3 REPLIES 3
Highlighted
robenanderson Frequent Visitor
Frequent Visitor

Re: Show value of one table as porcentage of another table value

are you using measures? measures should calculate what you want across tables if you have a relationship built between the two.

 

I would create a measure that counts the distinct operation.userId count and use that in a divide function with a measure in the users table counting the distinct userId. That function would then be used as the value for the chart. With the date as the axis, it should work out to show you what you're looking for.

luc Frequent Visitor
Frequent Visitor

Re: Show value of one table as porcentage of another table value

DIVIDE(DISTINCTCOUNT(Operations[Operation.UserId]), DISTINCTCOUNT(Users[User.UserId])) does result in a percentage but in the 0,09 format rather than 9%, which is somewhat acceptable but not ideal. I could multiply by 100 but how do I add the % sign?

 

I have no idea why but telling it to show as percentage shows exacly the same percentages I get when using operation.UserId which is really weird.

 

View solution in original post

robenanderson Frequent Visitor
Frequent Visitor

Re: Show value of one table as porcentage of another table value

if you select the field and go to the modeling tab in the top ribbon, you should be able to change the format of the numbers to a percent. That shouldn't change the numbers at all, so if that's happening, that's really interesting.

View solution in original post

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 285 members 2,947 guests
Please welcome our newest community members: