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

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
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite with Arun Ulag

Featured Session Drive Data Culture with Power BI: Vision, Strategy & Roadmap. Register here https://myignite.microsoft.com #MSIgnite #PowerPlatform #Power BI ​

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors