Summarize 3 tables into one in dax

[Anonymous]

Hello , i have 3 tables

here you can find the excelsheet : https://1drv.ms/x/s!Ag9tIyk2ofNRjkxmj4B_SvUhrK49?e=gk62w7

accounts, revenues , cost

i have inactive relationship between them

accounts and revenue has an inactive relationship on client name

accounts and cost has an inactive relationship on account name

here are the tables values
Accounts :

Client Name	Account Name
Client  A	Account A
Client  B	Account B
Client  C	Account C
Client  D	Account D

Revenue table :

Date	Client Name	Total Revnue
10/3/2022	Client A	245
10/4/2022	Client A	391
10/5/2022	Client A	256
10/6/2022	Client A	331
10/7/2022	Client A	325
10/8/2022	Client A	149
10/9/2022	Client A	125
10/3/2022	Client B	212
10/4/2022	Client B	320
10/5/2022	Client B	283
10/6/2022	Client B	208
10/7/2022	Client B	355
10/8/2022	Client B	246
10/9/2022	Client B	315
10/3/2022	Client C	391
10/4/2022	Client C	202
10/5/2022	Client C	134
10/6/2022	Client C	134
10/7/2022	Client C	147
10/8/2022	Client C	262
10/9/2022	Client C	400
10/3/2022	Client D	208
10/4/2022	Client D	371
10/5/2022	Client D	160
10/6/2022	Client D	158
10/7/2022	Client D	217
10/8/2022	Client D	183
10/9/2022	Client D	138

Cost table:

Date	Account Name	Price Per Client
10/3/2022	Account A	0.01
10/4/2022	Account A	0.01
10/5/2022	Account A	0.01
10/6/2022	Account A	0.014
10/7/2022	Account A	0.014
10/8/2022	Account A	0.014
10/9/2022	Account A	0.014
10/3/2022	Account B	0.012
10/4/2022	Account B	0.012
10/5/2022	Account B	0.012
10/6/2022	Account B	0.012
10/7/2022	Account B	0.015
10/8/2022	Account B	0.015
10/9/2022	Account B	0.015
10/3/2022	Account C	0.01
10/4/2022	Account C	0.01
10/5/2022	Account C	0.01
10/6/2022	Account C	0.01
10/7/2022	Account C	0.013
10/8/2022	Account C	0.013
10/9/2022	Account C	0.013
10/3/2022	Account D	0.014
10/4/2022	Account D	0.014
10/5/2022	Account D	0.014
10/6/2022	Account D	0.016
10/7/2022	Account D	0.016
10/8/2022	Account D	0.016
10/9/2022	Account D	0.016

My desired summarzied table result is:

Date	Client Name	Account  Name	Total Revnue	Cost Per Client
10/3/2022	Client A	Account A	245	0.01
10/4/2022	Client A	Account A	391	0.01
10/5/2022	Client A	Account A	256	0.01
10/6/2022	Client A	Account A	331	0.014
10/7/2022	Client A	Account A	325	0.014
10/8/2022	Client A	Account A	149	0.014
10/9/2022	Client A	Account A	125	0.014
10/3/2022	Client B	Account B	212	0.012
10/4/2022	Client B	Account B	320	0.012
10/5/2022	Client B	Account B	283	0.012
10/6/2022	Client B	Account B	208	0.012
10/7/2022	Client B	Account B	355	0.015
10/8/2022	Client B	Account B	246	0.015
10/9/2022	Client B	Account B	315	0.015
10/3/2022	Client C	Account C	391	0.01
10/4/2022	Client C	Account C	202	0.01
10/5/2022	Client C	Account C	134	0.01
10/6/2022	Client C	Account C	134	0.01
10/7/2022	Client C	Account C	147	0.013
10/8/2022	Client C	Account C	262	0.013
10/9/2022	Client C	Account C	400	0.013
10/3/2022	Client D	Account D	208	0.014
10/4/2022	Client D	Account D	371	0.014
10/5/2022	Client D	Account D	160	0.014
10/6/2022	Client D	Account D	158	0.016
10/7/2022	Client D	Account D	217	0.016
10/8/2022	Client D	Account D	183	0.016
10/9/2022	Client D	Account D	138	0.016

i appreciate your help

[tamerj1 · Accepted solution]

Hi @Anonymous 

You can start building the table by placing all the columns from the Revenue table then you can retrieve the Account Name and Cost per Client as follows

Account Name Measure =
CALCULATE (
    SELECTEDVALUE ( Cost[Account Name] ),
    USERELATIONSHIP ( Revenue[Client Name], Accounts[Client Nsme] ),
    USERELATIONSHIP ( Cost[Account Name], Accounts[Account Nsme] ),
    CROSSFILTER ( Revenue[Client Name], Accounts[Client Nsme], BOTH )
)

Cost per Client Measure =
CALCULATE (
    SELECTEDVALUE ( Cost[Cost per Client] ),
    USERELATIONSHIP ( Revenue[Client Name], Accounts[Client Nsme] ),
    USERELATIONSHIP ( Cost[Account Name], Accounts[Account Nsme] ),
    CROSSFILTER ( Revenue[Client Name], Accounts[Client Nsme], BOTH )
)