cancel
Showing results for
Did you mean:
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
Highlighted
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.

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.

3 REPLIES 3
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.

Highlighted
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.

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.

Announcements

#### Community News & Announcements

Get your latest community news and announcements.

#### Power Platform Summit North America

Register by September 5 to save \$200

#### Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 126 members 1,629 guests
Recent signins: