Forum Discussion
Syndicate_Admin
Administrator
4 years agojoining 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 ...
- 4 years ago
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
PaulDBrown
Community Champion
4 years agoCan you share some sample data?
- Syndicate_Admin4 years ago
Administrator
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