• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Creating Relationship with tables having further break down

chatter

New Member
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 ChannelTotal Revenue
Online
15.0​
POS
11.0​
 

Attachments

  • Pivot_MultipleTables.xlsx
    15.5 KB · Views: 3
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.
Code:
= 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.
Code:
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"
 

Attachments

  • Pivot_MultipleTables.xlsx
    129.1 KB · Views: 1
Back
Top