Forum Discussion
Administratorjoining tables or measures
Comrades good day:
I have a problem with a result that I have not been able to obtain.
I have two tables with shops and cities. The first table is where the stores make a presence with sales and the other is where the stores are authorized to have a physical premises.
What I want is that I can have a list of each store where the cities where they make a presence and where they have sales are unified.
Example: store 1 has sales in 14 cities but is only authorized to have physical stores in a single city, then the total would not be 15 cities but 14 because there is a city that agrees in the two tables.
| Shops | Presence cities | Authorized cities | Total |
| shop 1 | 14 | 1 | 14 |
| shop 2 | 144 | 144 | |
| shop 3 | 7 | 8 | 8 |
| shop 4 | 214 | 200 | 201 |
| shop 5 | 102 | 89 | 106 |
| shop 6 | 145 | 111 | 145 |
| shop 7 | 1 | 1 | |
| shop 8 | 1 | 1 |
Create two measures with DISTINCOUNT but when I try to join those two in a table I do not get the result I am looking for.
Do you have a recommendation?
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
9 Replies
PaulDBrownCommunity Champion
Can you share some sample data?
Syndicate_AdminTable of cities where stores had sales
Shop City Sales shop 1 47001 - SANTA MARTA 1 shop 1 47001 - SANTA MARTA 1 shop 1 11001 - BOGOTÁ D.C. 11229 shop 1 11001 - BOGOTÁ D.C. 1501 shop 1 11001 - BOGOTÁ D.C. 2847 shop 1 85001 - YOPAL 2 shop 1 08001 - BARRANQUILLA 2 shop 1 25377 - LA CALERA 3 shop 1 11001 - BOGOTÁ D.C. 223872 shop 1 13001 - CARTAGENA 3 shop 1 47001 - SANTA MARTA 5 shop 1 11001 - BOGOTÁ D.C. 9651 shop 1 66001 - PEREIRA 1 shop 1 44001 - RIOHACHA 1 shop 1 25754 - SOACHA 1 shop 1 20001 - VALLEDUPAR 1 shop 1 17001 - MANIZALES 1 shop 1 25286 - FUNZA 1 shop 1 23001 - MONTERIA 1 shop 1 13683 - SANTA ROSA 1 Table of cities where stores are authorized
Shop City Local shop 1 47001 - SANTA MARTA 1 Expected result
Shop Towns Local shop 1 14 1
- PaulDBrown
Sorry, 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?
- Syndicate_Admin
Hello;
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
Let's see if this works for you.
First create dimension tables for Stores and Cities. The model looks like this:
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
v-zhangtiCommunity Support