Forum Discussion
Revenue and Expenditure Table Variance
So I attached two dummy files since I cannot attach the real files. I have a table in Power BI that has the sales revenue by operating year and then I have a table in Power BI that has the expenses.
The common fields between each table include:
- Expense Center Code in the Sample Expenses Table corresponds to the Sales Center Code found in the Sample Revenue Table
- Expense Center in the Sample Expenses Table corresponds to the Sales Center found in the Sample Revenue Table
I can't create a relationship between the two since the Codes repeat in both on multiple rows.
What I am trying to do is take the total sales revenue for an operating year and subtract the expenses for an operating year to get the net difference by Sales Center/Expense Center. I still want to be able to see things like Expense Type and Sales Type along with the Object Group to see how that all plays into the bottomline for each location. How do I do this?
Sample Files:
Sales Expense Sample File: https://drive.google.com/open?id=1MMCSfXthjkYjhMZn2vR4E35oi0sWgsFC
Sales Revenue Sample File: https://drive.google.com/open?id=1sxyhfpqAR0MTG5hhH-rLc8HgybfBCX9R
So from the information, I want to be able to say something like:
- The Destin Center had $1,234,424 in sales and $4,232,766 in expenses presenting a variance of ($2,998,342). I just plugged random numbers in just to present an example of what I am trying to accomplish between the two tables in Power BI visuals.
I tried to simulate a case like yours, and this is the results...
14 Replies
- BILASolution
Solution Specialist
- JMWDBA
Advocate II
Oh no try the following https://drive.google.com/file/d/1MMCSfXthjkYjhMZn2vR4E35oi0sWgsFC/view?usp=sharing
- BILASolution
Solution Specialist
- wildmight2017
Advocate II
You should ideally create a unique dimension (select distinct code from your tables perhaps with UNION to include all possible scenarios)- and join both tables as a dimension. That should work.