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

princee's avatar
princee
Frequent Visitor
8 years ago

Many thanks Saurabh. I can see how this method works.  I would like this table to interact with tables on my report which look at the detail behind the sales. How can i do this? Would i need to create a relationship on Final output to table 1?

saurabh_kedia_'s avatar
saurabh_kedia_
Microsoft Employee
8 years ago

Ya! Actually you need to find a unique column which can be linked to the fat tables (Sales).

As far as I understand the same can be achived in this case by concatenating Attributes & CHK.

 

Regards,

Saurabh

  • princee's avatar
    princee
    Frequent Visitor
    8 years ago

    Sorry another question! I cant add the relationship because the FINAL table contains a blank row (null values). Im not sure why this is, possibly because some sales have no results in a year. How do i force the line to be removed. Is it using the query editor? I cant see it in my list of tables when i go into query editor.


    Thanks!

    • 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!