Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
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!
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
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