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

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?