joining tables or measures

[Syndicate_Admin]

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?

[Syndicate_Admin · Accepted solution]

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

[v-zhangti]

Hi, @Syndicate_Admin 

What should be the correct result for Total?

Best Regards

[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]

See the largest sample

Store Archive

[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.

[PaulDBrown]

Can you share some sample data?

[Syndicate_Admin]

Table 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