Creating Relationship with tables having further break down

[chatter]

Hi,
I have 3 tables which I want to relate in a way so that I can filter/extract the required information as explained below.

The first table is an income statement by products (4 products namely Motor, Marine, Medical, General). The first column has descriptions (Income, Expense, & G&A), the next 4 columns have product-wise values for each of the description item.

The second table is a further break down of each of the individual revenue item by sales channel. The first column in this table contains the 4 products. The 2nd column contains the revenue from Online channels. 3rd column contains revenue from Point of Sales

Similarly, the third table is a further break down of each of the individual expense item by sales channel.
The first column in this table contains the 4 products. The 2nd column contains the expense under Online channel. 3rd column contains expenses under Point of Sales channel

Can anyone guide me how to create a relationship among these tables so that I can filter or extract the following data using power pivot:

Sales Channel	Total Revenue
Online	15.0​
POS	11.0​

 

[Chihiro]

First, start by flattening out all your tables.

But how is 1st table supposed to relate to the other 2?

At any rate. Add custom column to Revenue & Expense after flattening it out.
Change "Revenue" to "Expense" for Expense table.

= Table.AddColumn(#"Renamed Columns", "Type", each "Revenue", type text)

Append Expense to Revenue. Load to data model and use it in Pivot.

No relationship is required. You only have fact tables and no dimension tables.

let
    Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Line of Business", type text}, {"Online", Int64.Type}, {"POS", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Line of Business"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Sales Channel"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Type", each "Revenue", type text),
    #"Appended Query" = Table.Combine({#"Added Custom", Expense})
in
    #"Appended Query"