Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I am trying to create the table below from Excel to Power BI. This table is a bit challenging as I need to bring together multiple tables into one and calculate the totals. To be more specific, I need to bring tables Income, Expenses and Loans into one single table in order to calculate the total revenue. However, this needs to filter different attributes in each section (i.e. in expenses I need to filter only expenses data and in loans I need to filter only loans data). Furthermore, in some parts of the table I have different granularities (i.e. in income I need to start from Areas and in Expenses I need to start from Buildings).
Is there any way to do this in Power BI? I fnot can you suggest any workarounds?
Please see the table in Excel. Any help would be much appraciated.
Hi @Anonymous,
In PBI you can achieve this using the query editor and making use of it's options for merging tables or make use of the relationship models and used different tables and then using measures to make the expected result, as for the different levels of granularities this can also be made if you setup your tables in the correct way using the query editor.
Without any data that you have I cannot give you much more options but created this sample based on your data not sure if this captures the full extent of your model.
Se the link for a sample file here.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHello and many thanks for your reply, This solution seems to work.
What I understood in this scenario I need to create separated tables for the aggregated table sections (i.e. income, expenses, rows) and then append these in order to create the aggregated one?
Hi @Anonymous,
As I said I don't know how you have you data setup in this case I did it with separated tables but if your information is all in one it can be made with what you have,
Can you provide a sample?
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português
Hello MFelix,
The information I have is all in one table. Please follow the link below to find some sample data.
https://drive.google.com/open?id=1ihUinqiJsLjT_2z7fRd9lbPKK9nAMNmM
Please let me know if you have any issues with the link.
Many thanks again,
MariosChr90
Hi @Anonymous,
can you please tell me what are the values you want to put on your table in your question you add Area Building and Floor, on this file you have Property, Category, Sub Category and Company. Do you also want to have all the Technology and Investments are to put on the table along side with the other Products?
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHello Mfelix,
Apologies, my example was not clear as I used different data in the example table and the sample data.
The example table has the output I want to achieve but the sample data has the same data structure with my raw data in the database.
Please see below another example I created based on the sample data I used. In this example I use the correct attributes and I want to highlight the challenge in creating an aggregated table in which some rows start from the Company, some others start with Property and some other times with the Category.
*please note that the numbers in the sample data nd example table are not the same
Hi @Anonymous,
Made a new table with the following structure:
Product ID Level_1 Level_2
Expenses | 2 | Property | Category |
Investments | 4 | Category | Sub-Category |
Technology | 5 | Category | Sub-Category |
Loans | 3 | Company | Sub-Category |
Income | 1 | Company | Property |
ID is just to determine the sort order and Level_1 and Level_2 is where you define what is the two levels to use in matrix table.
(In the products I didn't had information place a random value).
Then added two columns to the table:
Sorting_1 = SWITCH ( TRUE (); LOOKUPVALUE ( 'Summarize'[Level_1]; 'Summarize'[Product]; 'Sample data'[Product] ) = "Company"; 'Sample data'[Company]; LOOKUPVALUE ( 'Summarize'[Level_1]; 'Summarize'[Product]; 'Sample data'[Product] ) = "Property"; 'Sample data'[Property]; LOOKUPVALUE ( 'Summarize'[Level_1]; 'Summarize'[Product]; 'Sample data'[Product] ) = "Category"; 'Sample data'[Category]; LOOKUPVALUE ( 'Summarize'[Level_1]; 'Summarize'[Product]; 'Sample data'[Product] ) = "Sub-category"; 'Sample data'[Sub category]; BLANK () )
Sorting_2 = SWITCH ( TRUE (); LOOKUPVALUE ( 'Summarize'[Level_2]; 'Summarize'[Product]; 'Sample data'[Product] ) = "Company"; 'Sample data'[Company]; LOOKUPVALUE ( 'Summarize'[Level_2]; 'Summarize'[Product]; 'Sample data'[Product] ) = "Property"; 'Sample data'[Property]; LOOKUPVALUE ( 'Summarize'[Level_2]; 'Summarize'[Product]; 'Sample data'[Product] ) = "Category"; 'Sample data'[Category]; LOOKUPVALUE ( 'Summarize'[Level_2]; 'Summarize'[Product]; 'Sample data'[Product] ) = "Sub-category"; 'Sample data'[Sub category]; BLANK () )
Then just add this to your matrix.
See attach the PBIX file.
Any question please tell me.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português@Anonymous I would recommend to add a calculated column for filter and then use that as filter,
Filter Records = if(Table1[Account]="Loan" && Table1[Group] = "some value", 1, if(Table1[Account]="Rent" && Table1[Category]="some value', 1, 0) )
now use this field in filter with where value = 1
you can have your logic in this calculated column and drive what need to be filtered.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |