Mobile Home Sale Prices - First Project with PowerPivot

[sunhome]

Hi Y'all

I am brand new to Power Pivot ( just bought the Alchemy book by Mr Collie and Mr Jelen and the DAX book by Mr Collie ) and plan to spend some time learning. But, I need to get work done in the meantime, gotta look productive

Here is the outline of my 1st project and I am asking for help to determine how to achieve a Mobile Home Sale Price List, a real world project that I needed to have completed last week

1. I have an onsite table of tenants that I will need to sort/filter all blank values from the name column which would indicate a vacancy and the goal of this step would be to grab all spaces that are vacant.
2. Next, In order to sell the home, I must determine if we have the title. This is where our "Title" table needs to be checked to see if we own the title. This table has multiple entries per title, as we record the old title owner on one row, with the new title holder on another row. The factor that I can use to sort would be the date added column, as a home might have multiple rows of entries but the date added will be unique to each home. The goal here would be to locate the latest date added per home entry.
3. Then I need to calculate the money invested into the home in renovations and investments to make sure our ROI is above these costs. For this I do have another table called "Home Costs" which just lists the home space with the costs with dates of invoices/purchases.

These seem to be the three steps needed. Any suggestions to shorten the cycle of production of this project and to keep me on the right track would be great! Please chime in, also, if Power Pivot is not the ideal tool for this project as I am a layman excel user trying to learn some new skills

Regards, James

 

[sunhome]

Ok, here are the fields for each table being referenced ...

"OnSite" Table
Community Name
Site
Type
Type2
Name
First Name
Balance

"Title" Table
Community Name
Space #
Tax Roll #
Title Holder
Title Owner
Vin #
Year
Manufacturer
Make
Size
Date Of Title Added

"Cost" Table
Community Name
Space #
Investment Cost
Investment Date
Renovation Cost
Renovation Date

Regards, James

 

[scottsen]

I would start with Rob's DAX book. The alchemy book is more for "specific" problems (and inspiration), but isn't really a "general learning tool".

In the OnSite table, is there a Space#? (is that secretly the "Site" ?)

One thing you will find quickly is that you can not create a relationship between tables... via multiple columns. So, you need ...say.. a "Community Name + Space #" column, so that you can create a relationship on this unique id.

A better idea might be to have a Spaces table, that had Community Name, Space # and (say) a made up "Space Id" column, that can be used in other tables as your unique id, and you can use this Spaces table to filter other tables by Community Name.

It is possible this is the intent of the OnSite Table?

For the first time in 200 posts, I am going to blatantly suggest you use my services. I'm happy to continue answering your questions in the forums for the benefit of you and future generations of readers, however... you would really benefit from a 2 hour session with me to "kick start" your model. You would get both a working model and a ton of learning. Something to consider.

 

[sunhome]

Hi Scott, I just contacted you via your website regarding the 2 hour session.

The Onsite table comes directly from our Campgound software and uses the term "site" for the "space#". I did get some disappointing messages "the relationship cannot be created" which I did find some reading on the issue, A mystifying and awesome solution for many 2 many « PowerPivotPro

Regards, James

 

[scottsen]

Okay, so "Site" from the OnSite table is equivalent to Space# in the other tables?

You need a unique key to relate these tables. If Space# is already unique (say its a number prefixed with a community #) then you are good to go. Otherwise, you will need to either generate a totally bogus key (some unique int), or use a calculated column that is "community + space#" on each of your 3 tables.

Does the Onsite table have a unique row per Community+Space?