Parent-Child Sales Aggregation Issue

HI 
Let’s say we have a single DB table that has sales data at the state level and population data at the city level and is pulled into PYRAMID using the direct query model (CUSTOM QUERY TABLE). Now we want to show both state and city level information in the same grid visual. Here the population data can roll up and give the right result, but the sales data should ignore the city level and aggregate based on the state level. For example, State A has made sales of $10; hence, cities 1 and 2 are assigned with the same sales value in the DB table. This logic holds true for other states and cities as well. While computing the grand total for sales, the actual output should ideally be 60 since we need to ignore the city; however, the data rolls up and gives 120 as sales. Could you please share inputs on how this can be achieved?

DB DATA (Single Table)

ACTUAL PYRAMID OUTPUT

EXPECTED OUTPUT