Forum Discussion

princee's avatar
princee
Frequent Visitor
8 years ago
Solved

Summarising values from two tables

I am trying to create a new data table summarising value columns in another table by year.   For example i have a table                     Value2007 Value2008 Value2009  sales id 1         1   ...
  • saurabh_kedia_'s avatar
    saurabh_kedia_
    8 years ago

    Hi Prince,

     

    First in Query Editor, Unpivot other columns

    First, select the columns without dates as columns, then click on un-pivot columns and then click un-pivot other columns:

     

     

    The result will be as below:

     

     

     

    Then go to report view and type in the below for formula for new table:

    FINAL_OUTPUT = SUMMARIZE(Table3,Table3[Attribute],Table3[CHK],"VALUE",SUM(Table3[Value]))

     

     

    Regards,

    Saurabh Kedia

  • saurabh_kedia_'s avatar
    saurabh_kedia_
    8 years ago

    Hi,

     

    Please try this:

     

    FINAL_OUTPUT =  SUMMARIZE(Filter(Table3,Table3[Attribute] <> BLANK() && Table3[CHK]<>BLANK()),Table3[Attribute],Table3[CHK],"VALUE",SUM(Table3[Value]))

     

    Regards,

    Saurabh Kedia

saurabh_kedia_'s avatar
saurabh_kedia_
Microsoft Employee
8 years ago

Hi,

 

Please try this:

 

FINAL_OUTPUT =  SUMMARIZE(Filter(Table3,Table3[Attribute] <> BLANK() && Table3[CHK]<>BLANK()),Table3[Attribute],Table3[CHK],"VALUE",SUM(Table3[Value]))

 

Regards,

Saurabh Kedia

princee's avatar
princee
Frequent Visitor
8 years ago

Thanks Saruba. I now have another problem, as the source data has increased in rows -- it is corrupting some of my other data tables where there is a count/sum on other columns. Therfore im not sure i can use this solution.  Also this source data links to an excel file and when i refresh, Im not sure how this would work? Any advuce gratefully recieved!

  • saurabh_kedia_'s avatar
    saurabh_kedia_
    Microsoft Employee
    8 years ago

    Hi,

     

    I have used the same kind of formula..that too for .txt file i.e. with 3-4 million rows.

     

    It works perfectly fine. 

     

    As far as the error is concerned I think it is because of the relationship between two tables.

    Please check if one of the columns always have unique values & the relationship between tables does create a circular dependency (Can be solved in changing the join type i.e. "both" or "single" in some cases.)

     

    Regards,

    Saurabh Kedia