Summarising values from two tables

[princee]

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

[saurabh_kedia_ · Accepted solution]

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]

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.

[saurabh_kedia_ · Accepted solution]

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]

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

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]

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_ · Accepted solution]

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]

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

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