• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Pivot Table for sales data (real estate)

Bob G.

Member
I get a dump of real estate data and i am looking to put the data into a pivot table some the manger can look at agent production numbers. The issue is there is a "Listing" and a "Selling" agent. So I can make one table for both the listing agent and selling agent for numbers and sum and add the data together manually. What i have been doing is duplicating the data and make one entry for the seller and one for the listing agents to get around manually sum data (but it time intensive).

I want to know is there a way to have one PV table that count and sum the data for both the "Listing" and "Selling" agent?

$ ListingAgent SellingAgent
500 Agent 1 Agent 2
300 Agent 2 Agent 3
800 Agent 3 Agent 3
1200 Agent 4 Agent 1

Thanks you in advance for any and all help.
 
There are few ways to deal with this.

1. Split table into 2 tables (listing agent & selling agent) Use Agent dimension table to tie it together.
2. If you have PowerPivot, you can use table as is, but use 2 separate relationships to each agent column (from dimension table). One is active and the other defined through USERRELATIONSHIP().
3. Use Get & Transform to unpivot data and use attribute column.

How to best structure data will largely depend on your analysis/reporting need. I'd recommend uploading sample data set, along with manually created desired output. Make sure that your data set is representative of your actual data (with desensitized info, but keeping all columns and data types intact).

Oh, and don't forget to let us know which version of Excel that you are using. Solution will largely depend on it.
 
Chihiro,
I am using Excel 2016.

Attached is sample data. Report i am looking for is Total sales and total transaction. one PV that sum the listing and sales by agent name.
 

Attachments

  • Real_Sample_Data.xlsx
    185.3 KB · Views: 5
Are you looking for something like attached?

You can go to Data tab -> Queries & Connections to show data transformations done in Get & Transform (PowerQuery).

Then, all these queries are loaded into data model and used Data -> Data tools -> Relationships to build relationships.
You can check existing relationship by clicking on it.

Note: Since you are using Excel 2016, I wasn't sure if you are using SKU that has PowerPivot access... I avoided use of DAX measures and more complex models.
 

Attachments

  • Real_Sample_Data.xlsx
    798.4 KB · Views: 7
Select entire data range. Data -> Get & Transform -> From Table/Range.

Then in Query Editor....
1. Copy the query and paste it in at query pane (right most section).
2. Leave original query as is. But rename it "Listings"
3. Rename copied one to "Sales" and filter out (blank) from SallingAgent column.
4. Add blank query. Use formula to combine Agents from both Selling & listing and then reduce it to distinct list (refer to the Source step in Agents table).
5. Load all 3 queries into data model.
6. Build relationships between tables (refer to link for detail on that).
https://support.office.com/en-us/ar...in-excel-fe1b6be7-1d85-4add-a629-8a3848820be3
7. Using tables from data model (tables with orange cylinder icon in Pivot Table Field Selection pane), build your pivot table.

But as I stated, just go into Query Editor and Relationships to see how it's been built. Steps are all there.
 
Back
Top