Forum Discussion
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 2 2
sales id 2 2 4 2
etc
I want to table and chart the summarised data
2007 3
2008 6
2009 4
the top table has lots of rows, but the bottom will have 5/10.
How do I createthis second table in Power BI? I have looked at new table, new measure, sum functions but not finding it straightforward when i think it should be relatively simple!
Thanks for your help
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
8 Replies
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?
