Forum Discussion
Revenue and Expenditure Table Variance
I tried to simulate a case like yours, and this is the results...
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.
I tried to simulate a case like yours, and this is the results...
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.
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.
Removing the location name altogether made everything work!!!:smileyvery-happy:
Excellent :smileyhappy: