How to calculate sales for a store non selected

[Anonymous]

Hi All!

I would like to know if there is a function or formula to solve the following problem. I have a basic data model with a store table master (DimTienda, PK StoreKey), a fact sales table (FactSales, PK StoreKey) with sales for each store and a second master's table (DimTiendaDistancia, PK StoreKeyA) that for each store shows the distance to the other ones.

Note: The StoreKeyA and StoreKeyB fields in DimTiendaDistancia are the same store codes than StoreKey from the two other tables. For example, if we had only three stores with their StoreKey codes (Store1, Store2, Store3), the raws for the two first stores in the DimTiendaDistancia would be:

StoreKeyA | StoreKeyB | Distance

Store1 | Store2 | 3Km

Store1 | Store3 | 5Km

Store2 | Store1 | 3Km

Store2 | Store3 | 8Km

I would like to know how can I create a matrix table with the dimensions StoreKeyA (=StoreKey) and StoreKeyB BUT showing only sales for the StoreKeyB stores. Now I can only show sales (Valor) for StoreKeyA = 854 store but not for the StoreKeyB sotres (406, 1260, etc.).

Thank you very much! 

[Anonymous]

Hi @amitchandak !

I have checked the video and I am not sure is the solution I was looking for. Let me explain it to you again in order to see if exists some solution or workaround.

I have a table like the following where for each store it shows me the distance to the other ones (I get this information from DimTiendaDistance table): 

What I would like is to add another column showing the sales for StoreKeyB and NOT for the StoreKeyA without having to select any StoreKey.

The relation between them are the one I explained before. I have a basic data model with a store table master (DimTienda, PK StoreKey), a fact sales table (FactSales, PK StoreKey) with sales for each store and a second master's table (DimTiendaDistancia, PK StoreKeyA) that for each store shows the distance to the other ones.

Note: The StoreKeyA and StoreKeyB fields in DimTiendaDistancia are the same store codes than StoreKey from the two other tables. For example, if we had only three stores with their StoreKey codes (Store1, Store2, Store3), the raws for the two first stores in the DimTiendaDistancia would be:

StoreKeyA | StoreKeyB | Distance

Store1 | Store2 | 3Km

Store1 | Store3 | 5Km

Store2 | Store1 | 3Km

Store2 | Store3 | 8Km

Thank you in advance!! : )

[v-yiruan-msft · Accepted solution]

Hi @Anonymous ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want. If it is not applicable for your scenario, please provide your expected result with example and screenshots. Thank you.

Best Regards

[Anonymous]

Thank you very much @v-yiruan-msft !! This works for me!

Regards,

[amitchandak]

@Anonymous , for that you might need an independent table.

refer this video I dicussed the same case - https://youtu.be/lOEW-YUrAbE

[Anonymous]

@amitchandak Thanks! I will see the video and I will let you know if it works for me 😉