Forum Discussion
Pull data into matrix when datas only in the second table
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?
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
2 Replies
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/1447523Hi 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
