Showing percentage from two different datasets

[Mazex1985]

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.

[v-jingzhang · Accepted solution]

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.

[SpiroswayGR]

@Mazex1985 

Did you connect your tables with calendar table ? 

Can you show us the data model ? 🙂

[Mazex1985]

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