Forum Discussion
Summarising values from two tables
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
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
Also to add in some more detail, i would like the last column in second table to be split into 2 (secured/pipeline) based on a column specifying each row as one of these in table 1.
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
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?
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
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!