Excel for Decision Making
March 24, 2021
I have a data model with 3 tables:
Region (State, Region) *State is the Unique value*
WeatherEvent_Data(State, City, Peril, County,OccuranceNumber, Lat, Long, County, Zip) *No Unique Values*
CurrentMonth(State, City, Zip, LOB Type, Count) *No Unique Values*
The Relationships are as follows:
One to Many relations from Region Table: State to WeatherEvent_Data table: State
One to Many relations from Region Table: State to CurrentMonth table: State
I am trying to create a pivot table that shows the Region, State, Cities and Count by Peril where there are reported weather events (from the WeatherEvent_Data) as well as show the number of customers in each city. I created a Measure that shows the number of customers by city but only when I use the City field from the CurrentMonth table which then lists every city where we have customers rather than just the cities where a weather event was reported. The measure is listed below. When I use the City field from the WeatherEvents_Data table, the customer count shows the total customers for the state in each City row.
Also, ideally I would like the Customer count to only show one time instead of repeating after each Peril but I am not sure this is possible with Pivot Tables.
Any suggestions on what I can do to would be greatly appreciated!
July 16, 2010
Welcome to our forum!
Your file didn't contain your Power Pivot model, so it's tricky to answer your question, but let me try and describe it and hopefully you can visualise what I mean.
Your Region dimension table needs to contain the county, city and zip code fields, then you can remove all the location related fields, except the zip code field, from your Weather Event and Current month fact tables.
Region (State, Region, city, county, zip) *Zip is the Unique value*
WeatherEvent_Data(Peril, OccuranceNumber, Lat, Long, Zip) *No Unique Values* (Lat and Long are redundant fields so you could remove them too)
CurrentMonth(Zip, LOB Type, Count) *No Unique Values*
Then create relationships between the zip code fields in your fact tables and the Region dimension table.
When you build the PivotTable you must use the region fields from the region table, not the fact tables.
Hope that points you in the right direction.