CODEWITHSUNDEEP
powerbidaxpowerquery
Ralk
Ralk

Reputation: 461

Combining three tables PowerBI

I'm trying to solve a complex problem (at least for me and my level) and I would like to have your advice on how to deal with it.

I have three tables in my model: sales of cars by date in the first one (A), parts of each car in another one (B) and the prices of the mentioned parts in the last one (prices will depend on the date that we're buying the car -C-). The goal is to known the price of each car at each line.

Here are the tables:

Table A

Year Car Quantity
2021 A 2
2021 B 3
2022 A 1
2023 B 4

Table B

Car Part Quantity
A 1 5
A 2 14
B 1 7
B 3 9
C 2 25

Table C

Part Year Price
1 2021 1
2 2021 3
3 2021 2
1 2022 4
2 2022 5
3 2022 7
1 2023 4
2 2023 7
3 2023 9

The final table will look something like this:

Year Car Quantity Unit price Total price
2021 A 2 47 94
2021 B 3 25 75
2022 A 1 90 90
2023 B 4 108 432

The most important column is the 'Unit price' one but after trying to do it with DAX I'm completely KO and I'm not able to find a solution...

EDIT:

Here is the explanation on how the final column 'Unit price' is computed:

The unit price is computed as follows for the first line: the car model is A and it’s produced in 2021. We can go to Table B in order to know which parts are included in this kind of car and their quantity (parts 1 and 2 with quantities 5 and 14 respectively). As the car is produced in 2021 we go through Table C in order to recover their prices finding prices 1 and 3 for parts 1 and 2 respectively. At the end we calculate the unitary price as follows: 1x5+3x14 (price of the part 1 in 2021 multiplied by its quantity, the same for part 2) = 47

EDIT 2:

Here is the first DAX code I tried:

Measure = 
    VAR Aux0 = ADDCOLUMNS(Table_C;"key_car_year";[Car]&"-"&[Year])
    VAR Aux1 = ADDCOLUMNS(ADDCOLUMNS(CALCULATETABLE(Table_B;Table_B[Car]=[Car]);"Date";[Year]);"key_car_year";[Car]&"-"&[Year])
    VAR Aux2 = ADDCOLUMNS(Aux1;"Price";LOOKUPVALUE(Aux0[Price];Aux0[key_car_year];Aux1[key_car_year]))
RETURN
    SUM(Aux2[Price])

Upvotes: 1

Views: 211

Answers (1)

Smitteeh
Smitteeh

Reputation: 126

If your parts don't contain other parts. You can do the following:

  1. Make a price and product table. By joining table A and B you get all the required parts for each car. You can then make a PartID by concatting the part and in this case Year. Then also do the same for your price table (Table C).

  2. Add a relation betweeen the two PartID's in order to calculate the UnitPrice.

    Price = CALCULATE( SUMX('Table A', 'Table A'[Part Quantity] * Table C'[Price]) ) )

Product Table Price Table

This will result in the following interactive matrix in Power BI.

Result Interactive

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMlRQ0lFyBBFGSrE6CCEnEGEMEzKCqzKECRnDVZkoxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Year " = _t, #"Car " = _t, Quantity = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Year ", Int64.Type}, {"Car ", type text}, {"Quantity", Int64.Type}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Year ", "Year"}, {"Car ", "Car"}}),
#"Merged Queries" = Table.NestedJoin(#"Renamed Columns", {"Car"}, #"Table B", {"Car"}, "Table B", JoinKind.LeftOuter),
#"Expanded Table B" = Table.ExpandTableColumn(#"Merged Queries", "Table B", {"Part", "Quantity"}, {"Part", "Quantity.1"}),
#"Renamed Columns1" = Table.RenameColumns(#"Expanded Table B",{{"Quantity.1", "Part Quantity"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns1", "PartID", each Number.ToText([Part]) & "-" & Number.ToText([Year]))
in
    #"Added Custom"

If you want it to be a static table you can also join the price straight to the Product table and use that.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMlRQ0lFyBBFGSrE6CCEnEGEMEzKCqzKECRnDVZkoxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Year " = _t, #"Car " = _t, Quantity = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Year ", Int64.Type}, {"Car ", type text}, {"Quantity", Int64.Type}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Year ", "Year"}, {"Car ", "Car"}}),
#"Merged Queries" = Table.NestedJoin(#"Renamed Columns", {"Car"}, #"Table B", {"Car"}, "Table B", JoinKind.LeftOuter),
#"Expanded Table B" = Table.ExpandTableColumn(#"Merged Queries", "Table B", {"Part", "Quantity"}, {"Part", "Quantity.1"}),
#"Renamed Columns1" = Table.RenameColumns(#"Expanded Table B",{{"Quantity.1", "Part Quantity"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns1", "PartID", each Number.ToText([Part]) & "-" & Number.ToText([Year])),
#"Merged Queries1" = Table.NestedJoin(#"Added Custom", {"PartID"}, #"Price Table", {"PartID"}, "Price Table", JoinKind.LeftOuter),
#"Expanded Price Table" = Table.ExpandTableColumn(#"Merged Queries1", "Price Table", {"Price"}, {"Price"})
in
    #"Expanded Price Table"

Upvotes: 0

Related Questions