Connencting 2 rows to one row in another table

[Balázs_Fenyő]

I have a huge table with data like this:

date	order_id	slot_id	views
2020-11-12	1	1	10
2020-11-12	1	2	10
2020-11-12	2	1	10

And so on.

I have another table where I have sales values for this data like this:

sale_id	value
1	10
2	12
3	15

And the I have another table that connects these 2 tables, but I have rows where I have two (or more) sale_ids to one order_id, and rows where there is no sale_id because it is not associated to this table. Like this:

order_id	sale_id1	sale_id2	sale_id3	sum_value
1	2	3	null
2	1	null	null
3	null	null	null

I would like to see the sum_value of the order in some way and, this is probably a second problem, also know the value of 1 view, which in this case is the (sum_value / sum_views).

Looked all around this forum but just couldn't get my head around it. Any help is appreciated.

[Icey · Accepted solution]

Hi @Balázs_Fenyő ,

Please check if this is what you want:

1. UnPivot.

2. Create relationships.

3. Create calculated columns or measures.

Columns:

sum_value =
CALCULATE (
    SUM ( Sales[value] ),
    ALLEXCEPT ( ConnectTable, ConnectTable[order_id] )
)

sum_views =
CALCULATE (
    SUM ( Views[views] ),
    ALLEXCEPT ( ConnectTable, ConnectTable[order_id] )
)

sum_value / sum_views = DIVIDE( [sum_value], [sum_views])

Measure:

sum_value / sum_view = 
VAR sum_value =
    CALCULATE (
        SUM ( Sales[value] ),
        ALLEXCEPT ( ConnectTable, ConnectTable[order_id] )
    )
VAR sum_view =
    CALCULATE (
        SUM ( Views[views] ),
        ALLEXCEPT ( ConnectTable, ConnectTable[order_id] )
    )
RETURN
    DIVIDE ( sum_value, sum_view )

Best regards

Icey

If this post helps, then consider Accepting it as the solution to help other members find it faster.

[amitchandak]

@Balázs_Fenyő , not very clear. You can unpivot the third table and join it with one and two

[Balázs_Fenyő]

Yes, but then order_id won't be unique in this table