cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

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

2 ACCEPTED SOLUTIONS

Accepted Solutions

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:

 

Step1.png

 

The result will be as below:

 

 Step2.png

 

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

 

Step3.png

 

Regards,

Saurabh Kedia

View solution in original post

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

View solution in original post

8 REPLIES 8
Frequent Visitor

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:

 

Step1.png

 

The result will be as below:

 

 Step2.png

 

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

 

Step3.png

 

Regards,

Saurabh Kedia

View solution in original post

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!

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

View solution in original post

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!

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

 

 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors