Forum Discussion
Showing percentage from two different datasets
Hello,
I have three tables like the below
Users
Earnings
Expenses
I am showing one table on powerbi, that shows each user with his earnings and expenses next to him, I want to add another column that shows the percentage of earnings over expenses, but when i add that column (sum(earnings)/sum(expenses) to the users table, it's showing percentage for all users, I need it to be filtering per user, am i doing it wrong
Thanks.
Hi Mazex1985
Do you create relationships between Users table and other two tables based on User columns? I would recommend using a measure to get the percentage.
Percentage = DIVIDE(SUM(Earnings[Earnings]),SUM(Expenses[Expenses]))You can also get it with a calculated column in Users table. Here is a sample pbix.
Column = DIVIDE(SUMX(FILTER(Earnings,Earnings[Users]=EARLIER(Users[Users])),Earnings[Earnings]),SUMX(FILTER(Expenses,Expenses[Users]=EARLIER(Users[Users])),Expenses[Expenses]))Kindly let me know if this helps.
Community Support Team _ Jing Zhang
If this post helps, please consider Accept it as the solution to help other members find it.
4 Replies
- SpiroswayGR
Resolver III
No, I am not using any calendar there, why there shall be a calendar? its not dates dependent.
Thanks
- SpiroswayGR
I was thinking if you wanna work around with time measures for expences or earning.
In that case , either you will try to use cross filter direction = both , so you will able to filter then (not so good solution if you want to expand with more tables and measures).
Better solution is to use these dax (relate and userelathioship) like below : https://stackoverflow.com/questions/14366192/using-related-function-in-dax-with-userelationship
v-jingzhangCommunity Support
Hi Mazex1985
Do you create relationships between Users table and other two tables based on User columns? I would recommend using a measure to get the percentage.
Percentage = DIVIDE(SUM(Earnings[Earnings]),SUM(Expenses[Expenses]))You can also get it with a calculated column in Users table. Here is a sample pbix.
Column = DIVIDE(SUMX(FILTER(Earnings,Earnings[Users]=EARLIER(Users[Users])),Earnings[Earnings]),SUMX(FILTER(Expenses,Expenses[Users]=EARLIER(Users[Users])),Expenses[Expenses]))Kindly let me know if this helps.
Community Support Team _ Jing Zhang
If this post helps, please consider Accept it as the solution to help other members find it.
