Forum Discussion

JMWDBA's avatar
JMWDBA
Icon for Advocate II rankAdvocate II
8 years ago
Solved

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. 

14 Replies