Pull data into matrix when datas only in the second table

[DarylRob]

Hi, 

So I've created a matrix with the below columns

Table1.Account Manager Name, Table1.Client Name, Table1.Client Office, Table1.Revenue1, Table2.Revenue2

The two tables have the below data

Table1:

Account Manager Name, Client Name, Client Office, Revenue1

Table2:

Account Manager Name, Client Name, Client Office, Revenue2

Relationships:

Table1.Account Manager Name = Table2. Account Manager Name

Table1.Client office = Table2. Client Office

Client Name won't match

I want to be able to have a matrix that has a hierachy of

Account Manager > Client Name > Client Office

and when the client office match up then Revenue1 and Revenue2 will be on the same row 

Will I have to create new tables with all the values in and use that?

[Anonymous · Accepted solution]

Hi @DarylRob ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want.

1. Create the relationship base on the field Account Manager Name

2. Create a measure as below to get the revenue2

NRevenue2 =
CALCULATE (
    SUM ( 'Table2'[ Revenue2] ),
    FILTER (
        'Table2',
        'Table2'[ Client Office] = SELECTEDVALUE ( 'Table1'[ Client Office] )
    )
)

Best Regards

[lbendlin]

Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to work with. Avoid posting screenshots of your source data if possible.

Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523