Forum Discussion
Need help: Matrix with two tables
Hello everybody!
I'm having trouble creating a matrix view using two different tables but with the same fields in the summary lines.
I have tables like these:
SALES:
| SELLER ID | SALE VALUE (R$) |
| 1 | 20 |
| 1 | 30 |
| 1 | 50 |
| 2 | 50 |
| 2 | 40 |
| 2 | 50 |
| 3 | 200 |
| 3 | 25 |
| 3 | 50 |
OVERTIMES:
| SELLER ID | OVERTIME (HOURS) |
| 1 | 1 |
| 1 | 2 |
| 2 | 3 |
| 3 | 2 |
| 3 | 3 |
| 3 | 5 |
Both report different things, but share the same SELLER ID.
I would like to create a matrix to view both tables summarized, like this:
| SELLER ID | SUM OF SALE VALUE (R$) | SUM OF OVERTIME (HOURS) |
| 1 | 100 | 3 |
| 2 | 140 | 3 |
| 3 | 275 | 10 |
However when I try to add values from a second table in the matrix, it looks like this:
| SELLER ID | SUM OF SALE VALUE (R$) | SUM OF OVERTIME (HOURS) |
| 1 | 100 | 16 |
| 2 | 140 | 16 |
| 3 | 275 | 16 |
Anonymous,
This can be achieved with a star schema. Create a SELLERS table using Power Query or DAX. Here's a DAX calculated table:
SELLERS = DISTINCT ( UNION ( DISTINCT ( SALES[SELLER ID] ), DISTINCT ( OVERTIMES[SELLER ID] ) ) )Create relationships:
In the visual, use SELLERS[SELLER ID]:
2 Replies
Anonymous,
This can be achieved with a star schema. Create a SELLERS table using Power Query or DAX. Here's a DAX calculated table:
SELLERS = DISTINCT ( UNION ( DISTINCT ( SALES[SELLER ID] ), DISTINCT ( OVERTIMES[SELLER ID] ) ) )Create relationships:
In the visual, use SELLERS[SELLER ID]: