Revenue and Expenditure Table Variance

[JMWDBA]

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. 

[wildmight2017]

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.  

[BILASolution]

Hi @JMWDBA

The first link is broken

[JMWDBA]

Oh no try the following https://drive.google.com/file/d/1MMCSfXthjkYjhMZn2vR4E35oi0sWgsFC/view?usp=sharing  

[BILASolution]

Hi @JMWDBA

Try this...

https://1drv.ms/f/s!AuU-Ye8UGM4Rko0dqnEcUuSpNXL6vw

Regards

BILAsolution

[JMWDBA]

I am getting one error but I am thinking because I probably should have given more dummy data in the file.  

The error message when creating the Sales Center column in the Center Code created table says: "A table of multiple values was supplied where a single value was expected."

The real file has 150 locations that repeat by operating year.  There are other fields in the report but not really relevant.  The ones provided in the dummy file are the ones that I really focus on. 

Any thoughts?

[BILASolution]

@JMWDBA

Additionally, I think that error is because of there are different Center's names for the same Center Code in Revenue table. Check it.

[JMWDBA]

As I look at it, this is indeed the case.  When a location may have moved in a previous operating year, it kept the same number but took on the name of where it moved.  For accounting purposes, the name in effect for that operating year remained instead of being overridden even if it was in the same city and just moved to a different address.  

Then I have discovered that two of our locations have no code at all.  So truly now the only thing consistent between both tables is the name of the location within an operating year.   What do I do with this?

[BILASolution]

@JMWDBA

I need your real data to do a better analysis

[JMWDBA]

That becomes my conundrum.  I cannot obtain clearance to transmit the actual file.  Is it possible to leave out the Location Description and just use the Codes?  The codes I know are unique but something like Jacksonville - Main could have once been called Jacksonville - Mayport last year and the year before that it was Jacksonville - Matthews.  So the name changed but the code always remained the same. 

[BILASolution · Accepted solution]

@JMWDBA

I tried to simulate a case like yours, and this is the results...

https://1drv.ms/f/s!AuU-Ye8UGM4Rko0dqnEcUuSpNXL6vw

[JMWDBA]

Removing the location name altogether made everything work!!!

[BILASolution]

Excellent

[JMWDBA]

Ok I see why its doing totals for both instead of by location.  It wont establish the relationship because it needs at least one of the tables to have distinct values.  

[JMWDBA]

Yess!!! Its 99% there.  Now that I am no longer getting errors, what I notice its doing for the Sum of Revenue is taking the SUM across all 5 years worth of data I have in the revenue file which totals $527,899,365.06 instead of revenue just for the corresponding location. It's also doing the same thing for Expenses. 

I am going back through the pbix you provided to see if I missed something because I see that yours actually takes it for the specific location.