I'm new to Power Query and Power Pivot. I'm creating a dashboard, and having some trouble mastering the relationships between tables. There's some constraints / database logic at work.
In this simplified example I'd like to create a pivot table that shows the "Client Rate" applicable to the "Named Resource"
e.g. Amy, is an actor. Actors are classified as Entertainment. Entertainment has a client rate of $150. Therefore I'd like to show in a pivot table Amy, and the rate of $150. Refer to the image attached
Table A
Named Resource, Role A
Amy, Actor
Cristina, Bar person
Table B
Role B, Classification
Actor, Entertainment
Bar person, Hospitality
Table C
Classification, Client Rate
Entertainment, $150
Hospitality, $180
The tables are joined 1 to many on Role (Tables A to B), and Classification (Tables B to C).
When I create a pivot table with
- Named Resource & Role A in rows, and
- Sum of Client rate in Values,
- The table contains these values
- Named Resource, Role A, Sum of Client Rate
- Amy, Actor, 330
- Cristina, Bar person, 330
- I would like it to show these values
- Named Resource, Role A, Sum of Client Rate
- Amy, Actor, 150
- Cristina, Bar person, 180
- The table contains these values
Hope that makes sense. Can you help me achieve the above result, and understand the database logic that makes it work the way it does?
Hi Derek,
Welcome to our forum! Please upload your sample Excel file shown in the screenshots so we don't have to recreate your data to help you.
Thanks,
Mynda
Hello Derek,
I understand what you are trying to achieve, but even though Excel can handle big data in the Data Model it is not a relational database as such.
Relationships in the Data Model works when you have a 1 to many relation, where the tables on the many side are the tables where you want to get the values from and where the tables on the 1 side are those which you want to sort or filter by.
Based on your sample data (which in reality is to small to be separated like this) you should have following tables.
Table A
Named resource, Role, Internal rate, Client rate
Table B
Role, Classification
Br,
Anders
Thanks Mynda and Anders,
I was assuming because the 3 tables were linked that I could then just data from table 1 to get related data from table 3. From my experimenting and your answer Anders I can see that this is not the way forward.
Instead I used the Join function. A left join gave me the connections I needed to show the person, and their rate, and to also keep the data tables in the same structure.
Cheers,
Derek
