Reputation: 872
Imagine the following situation:
I am designing a data warehouse / Tabular cube/model for a broker company. This means that when the broker creates a Contract, there is a Buyer and a Seller.
So when I create my Fact_Contracts it looks something like this:
CREATE TABLE [dbo].[Fact_Contracts](
[FactContractId] [INT] IDENTITY(1,1) NOT NULL,
[ContractBuyer_Client_Key] [INT] NOT NULL,
[ContractSeller_Client_Key] [INT] NOT NULL,
[ContractDate_Key] [INT] NOT NULL,
[ContractAmount] [DECIMAL](18, 2) NOT NULL,
)
Now I have my Clients Dimension:
CREATE TABLE [dbo].[Dim_Clients](
[ClientKey] [int] IDENTITY(1,1) NOT NULL,
[ClientBK] [int] NOT NULL,
[ClientName] [nvarchar](20) NOT NULL,
)
Let's say I have 1 contract where ContractBuyer_Client_Key = 1 and ContractSeller_Client_Key = 2 and the ContractAmount = 1000.
Now when I look in my Tabular Cube, I would like to create a report, showing me details about my customers and my ContractAmount. The output should be like this:
FactContractId Client_Key ClientName ContractAmount
10 1 Abraham 1000
10 2 Björk 1000
--------------------------------------------------------
Total 1000
--------------------------------------------------------
Is it possible to create such a reciprocity report where I combine two different columns from my Fact-table?
Please note: A solution where I divide the Total by 2 is not an option. There are other issues which makes this undoable: Sometimes I have a third party as part of the contract which has not been shown on this simplistic outline of my tables. So my total needs to be the sum of all unique Contracts.
Upvotes: 0
Views: 38
Reputation: 872
Marmite Bomber did put me in the right direction, but the answer was not 100% correct.
Creating a contract_role view as mentioned is correct. But instead of just adding data from one role, I have instead done it in dax, using a SUMMARIZE:
SumOfAmount :=
CALCULATE(
SUM(Fact_Contracts[ContractAmount]),
SUMMARIZE(
contract_role,
Fact_Contract[FactContractId]
)
)
This seems to work even if I have more roles than just a Buyer and a Seller.
Upvotes: 0
Reputation: 21053
This is your fact table
select * from contract;
BUYER_ID SELLER_ID AMOUNT
---------- ---------- ----------
1 2 1000
Create a role view on a top of it
create view contract_role as
select 'Buyer' role_id, BUYER_ID client_id, amount from contract
union all
select 'Seller' role_id, SELLER_ID client_id, amount from contract;
select * from contract_role;
ROLE_I CLIENT_ID AMOUNT
------ ---------- ----------
Buyer 1 1000
Seller 2 1000
Now you may report all roles, but consider the total only from one of the roles
select CLIENT_ID, AMOUNT,
sum(case when ROLE_ID = 'Buyer' then AMOUNT end) over ( ) as total_amount
from contract_role
CLIENT_ID AMOUNT TOTAL_AMOUNT
---------- ---------- ------------
1 1000 1000
2 1000 1000
Upvotes: 1