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_Admin
Administrator
4 years agoTable 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 |