Show value of one table as percentage of another table value

[luc]

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?

[luc · Accepted solution]

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.

[robenanderson]

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 · Accepted solution]

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.

[robenanderson · Accepted solution]

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.