Scenario 1. There are Accounts and Customers tables and the bridge table Cross that relates each customer with multiple accounts and each account to multiple customers.
ACCOUNTS
AccountID Amount
A 10
B 20
C 45
CUSTOMERS
CustomerID Name
X Marco
Y Hal
Z Serge
CROSS
CustomerID AccountID
X A
X B
Y B
Z C
In order to have a measure that defines the sum of [Amount] for different set of Accounts and/or users you need to define the following relationships and measures:
Relationships:
Cross[CustomerID] -> Customer[CustomerID]
Cross[AccountID] -> Accounts[AccountID]
Measures:
‘Accounts’[Amt] = SUM(‘Accounts’[Amount])
‘Cross’[CrossCnt] = COUNTROWS(‘Cross’)
‘Accounts’[Sum of Amount] = [Amt](FILTER(‘Accounts’, [CrossCnt]>0))
The ‘Accounts’[Sum of Amount] measure will calculate the sum of amount for selected context, that is the subset of selected customers and/or accounts.
Scenario 2. Table Sales has related tables Customer, Product and Time. Table ExchangeRates has related tables Time and Currency.
CUSTOMERS
CustomerID Name
C1 Marco
C2 Hal
C3 Sege
PRODUCTS
ProductID Name
P1 Cable
P2 Monitor
P3 Case
TIME
TimeID Time
T1 10/10/2000
T2 10/11/2000
T3 10/12/2000
CURRENCIES
CurrencyID Short Name
CY1 EUR
CY2 AMD
CY3 CAD
EXCHANGE RATES
CurrencyID TimeID Rate
CY1 T1 1.32
CY2 T1 0.028
CY3 T1 0.91
CY1 T2 1.31
CY2 T2 0.029
CY3 T2 0.89
CY1 T3 1.3
CY2 T3 0.028
CY3 T3 0.88
SALES
CustomerID ProductID TimeID Amount
C1 P1 T1 100
C2 P1 T2 230
C3 P2 T3 75
C1 P3 T2 160
C2 P3 T2 190
C3 P3 T2 190
C1 P1 T3 250
C2 P2 T3 75
C3 P1 T3 250
In order to get the sum of [Amount] for different set of Products, Customers and/or Time you need to define the following relationships and measures:
Relationships:
Sales[CustomerID] -> Customers[CustomerID]
Sales[ProductID] -> Products[ProductID]
Sales[TimID] -> Time[TimID]
ExchangeRates[CurrencyID] -> Currencies[CurrencyID]
ExchangeRates[TimeID] -> Time[TimID]
Measures:
Sales[Sum of Amount] = SUM([Amount])
Sales[Converted Amount] = SUMX(‘TIME’, [Sum of Amount] / VALUES(ExchangeRates[Rate]))
NOTE: In order to get Sales[Converted Amount] value you need to place it on values of PivotTable while you have Currency set either on Row or Column labels, or is added to slicer and a single Currency is selected, otherwise it has no meaning to convert the amount to non-specified currency.
Scenario 3. Table Sales has related tables Customer, Product and Time. Table CustomerDemographics has related tables Customer, Demographics
CUSTOMERS
CustomerID Name
C1 Marco
C2 Hal
C3 Sege
PRODUCTS
ProductID Name
P1 Cable
P2 Monitor
P3 Case
TIME
TimeID Time
T1 10/10/2000
T2 10/11/2000
T3 10/12/2000
SALES
CustomerID ProductID TimeID Amount
C1 P1 T1 100
C2 P1 T2 230
C3 P2 T3 75
C1 P3 T2 160
C2 P3 T2 190
C3 P3 T2 190
C1 P1 T3 250
C2 P2 T3 75
C3 P1 T3 250
DEMOGRAPHICS
DemographicsID Name
D1 Canada
D2 Armenia
D3 Australia
CUSTOMERDEMOGRAPHICS
CustomerID DemographicsID
C1 D1
C2 D1
C3 D2
C1 D3
C2 D3
In order to slice the sum of sales amount by demographics you need the following relationships and measures.
Relationships:
Sales[CustomerID] -> Customers[CustomerID]
Sales[ProductID] -> Products[ProductID]
Sales[TimID] -> Time[TimID]
CustomerDemographics[CustomerID] -> Customers[CustomerID]
CustomerDemographics[DemographicsID] -> Demographics[DemographicsID]
Measures:
Sales[Sum of Amount] = SUM([Amount])
Sales[Sum of Amount by Geography] = [Sum of Amount](FILTER(Customer, COUNTROWS(RELATEDTABLE(CustomerDemographics))>0))
NOTE: If you have multiple related tables that you want to use for slicing the data, you will need to add filters to the following expression = [Sum of Amount](FILTER(Customer, COUNTROWS(RELATEDTABLE(CustomerDemographics))>0), FILTER(…),FILTER(…), …).