locked
Relationship among 4 tables RRS feed

  • Question

  • Hello there,

    I have an issue with Powerpivot and I appreciate any help on this regard.

    Basically I have 4 tables: Sales,Sales detail, product info and product subcategory.

    I have linked all 4 like this:

    Sales 1-->--*  Sales Detail

    Product Info 1-->--* Sales Detail

    Product subcategory 1-->--* Product Info 

    So I want to get Sales by Product subcategory however they are not directly linked. I cannot link them directly because there's no common key.

    Is there a workaround this ?

    Thanks in advance !

    Rgds

    Thursday, June 16, 2016 10:02 PM

Answers

  • You can do this:

    SumByCategory:=CALCULATE(sum(SalesDetail[Amount]),SalesDetail)

    Basically, you specific the table to be lookup table instead of from the primary table.

    When you create pivot table, put the field "SumByCategory" on data section, put Sales Number key on Rows Section, and put Category on Columns section.  You can drag column from other table.  

    Hopefully this work for you.

    • Proposed as answer by Michael Amadi Tuesday, June 21, 2016 5:32 AM
    • Marked as answer by Charlie Liao Thursday, June 30, 2016 2:13 AM
    Saturday, June 18, 2016 2:19 AM
  • Measures are universal across the data model. You can refer to a measure in one table even though it is not directly linked. SO if you have sales category as a dimension on the pivot table, the measure SUM(SALES_AMOUNT) in the sales table would automatically subtotal by category either across or down depending where you put category. Using commands like ALL you can even get the measure value ignoring the current report selections, for example, to compute the percentage of sales for the year, the ALL syntax would be used and compared to the current month that might be the subject of the report.
    • Proposed as answer by Charlie Liao Saturday, June 25, 2016 7:26 AM
    • Marked as answer by Charlie Liao Thursday, June 30, 2016 2:13 AM
    Monday, June 20, 2016 4:31 PM

All replies

  • You should be fine when adding a measure for Sales, e.g. SUM('Sales detail'[Amount]). You'll see that when adding columns from both the Sales and the Product subcategory tables to a pivot table, you'll get all combinations of values; but when adding a measure to the pivot table, only the combinations remain for which there is a Sales result.
    Friday, June 17, 2016 10:00 AM
    Answerer
  • Hello Michiel,

    Thanks for your reply !

     I am not really sure about your instructions.

    The Sales Amount information is only in the Sales table. The Sales Detail is linked with Sales by Order Id. Product is linked to Sales Detail by product ID and Sales Sub category is linked to Product by Subcategory ID. You see that between the tables there is only one connection, one to many.

    I am not sure how I should create a measure like you mentioned.

    Could you explain a little more ?

    Thanks in advance.

    Friday, June 17, 2016 10:24 PM
  • You can do this:

    SumByCategory:=CALCULATE(sum(SalesDetail[Amount]),SalesDetail)

    Basically, you specific the table to be lookup table instead of from the primary table.

    When you create pivot table, put the field "SumByCategory" on data section, put Sales Number key on Rows Section, and put Category on Columns section.  You can drag column from other table.  

    Hopefully this work for you.

    • Proposed as answer by Michael Amadi Tuesday, June 21, 2016 5:32 AM
    • Marked as answer by Charlie Liao Thursday, June 30, 2016 2:13 AM
    Saturday, June 18, 2016 2:19 AM
  • Hello Phili,

    I created the measure CALCULATE(SUM(Sales(Amount),SalesDetail) and it Worked ! Thanks !

    I am still trying to understand the logic of it (using a measure to link the tables) but anyway my problem is solved.

    Thanks a lot for your help !

    Regards

    Saturday, June 18, 2016 3:38 AM
  • Measures are universal across the data model. You can refer to a measure in one table even though it is not directly linked. SO if you have sales category as a dimension on the pivot table, the measure SUM(SALES_AMOUNT) in the sales table would automatically subtotal by category either across or down depending where you put category. Using commands like ALL you can even get the measure value ignoring the current report selections, for example, to compute the percentage of sales for the year, the ALL syntax would be used and compared to the current month that might be the subject of the report.
    • Proposed as answer by Charlie Liao Saturday, June 25, 2016 7:26 AM
    • Marked as answer by Charlie Liao Thursday, June 30, 2016 2:13 AM
    Monday, June 20, 2016 4:31 PM
  • Hello,

    I understood the concept and it has been ok so far on the measures I am creating. However I came across a problem with the following measure to get the  same month last year sales amount :

    =CALCULATE(SUM('Sales SalesOrderHeader'[TotalDue]);'Sales SalesOrderDetail';DATEADD('CALENDAR'[DATE];-1;QUARTER)) 

    If I slice the measure BY CATEGORY, YEAR, MONTH in the pivot table, the results of this measure come empty.

    By comparison the measure bellow for Year to Date works fine:

    =CALCULATE(SUM('Sales SalesOrderHeader'[TotalDue]);'Sales SalesOrderDetail';DATESYTD('CALENDAR'[DATE]))

    Can you point what am I doing wrong ?

    Thanks in advance


    Monday, July 4, 2016 11:14 PM