Pivot Table w/data from 2 tables to show only results where both cities match|Power Query|Excel Forum|My Online Training Hub

Hello,

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.

 =CALCULATE(Count(CurrentMonthPIF[Policy Count]),Regions,CurrentMonthPIF,WeatherEvent_Data)

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!