DAX measures between to tables - city, region and country comparison

12 Views Asked by At

I have two data tables, one is contains the city properties (called A) (for example region, etc.) and the other is contains the measures (called B). In table B, there is a column which marks the records as to what level they are; the values are "C", "R" and "T", meanings "city", "region" and "country". Between A and B has an one-to-many connection.

I'd like to write two measures in DAX which returns:

  1. how much the city's measure is smaller or larger than the region average (there are 20 records with value 'R' )
  2. how much the city's measure is smaller or larger than the country average (there is only one record with value 'T')

I try this, but is not working if I choose more than 1 city. I'd to make table output with the difference group by the cities.

Measure for the city:

_MEASURE_CITY = CALCULATE(SUM(PILOT[MEASURE]), PILOT[TYPE] = "I")

Measure for the city region:

_MEASURE_REGION= 
var region = CALCULATE(MAX(PILOT[CITY_REGION]), FILTER(CITY,CITY[ID] = SELECTEDVALUE(CITY[ID])))
return 
CALCULATE(SUM(PILOT[MEASURE1]), PILOT[TYPE] = "R", PILOT[CITY_REGION] = region, ALL(CITY[ID]))

Difference :

Diffrence = [_MEASURE_CITY] - [_MEASURE_REGION]
0

There are 0 best solutions below