Forum Discussion
Administratorjoining tables or measures
Let's see if this works for you.
First create dimension tables for Stores and Cities. The model looks like this:
And it creates the following measures:
For the number of physical stores
Tiendas Físicas = DISTINCTCOUNT('Listado de Tiendas'[Ciudad])For the number of cities with presences
Ciudades con Ventas = DISTINCTCOUNT('Ventas'[Ciudad])And for the total calculation
Total = VAR FT = VALUES ( 'Listado de Tiendas'[Ciudad] ) VAR CV = VALUES ( Ventas[Ciudad] ) RETURN COUNTROWS ( DISTINCT ( UNION ( FT, CV ) ) )I attach the sample PBIX file
Community ChampionSorry, can you provide a wider sample? I Take it that there are more values than shop 1 (is Shop 1 a store brand?) and that other shops can be selling in the same cities?
AdministratorHello;
In each city there may be several stores and tambein each store can make sales to the same cities where other stores also make sales.
What a shame not to be able to give the data but it is that as it is confidential information I have to invent data to be able to replicate the real problem. I will try to replicate more data.
- PaulDBrown
Just add some rows to what you already have with made up cities, store number etc which reflect what you have in the actual data. I'm still not sure if "Shop 1" is a shop ID
- Syndicate_Admin
Store 1 is the name of the store, there is also a store 2, store 3 and so on.
I'm finishing a sample file to share.
- Syndicate_Admin