Need help: Matrix with two tables

[FranciscoHoff]

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

[DataInsights · Accepted solution]

@FranciscoHoff,

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]:

[FranciscoHoff]

Hi @DataInsights 

It worked like a charm. 

Thanks!