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.

 

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.

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.

 

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.

Helpful resources

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.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 162 members 1,609 guests
Please welcome our newest community members: