Creating single table with different filters and granularity in each section

[Anonymous]

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.

[MFelix]

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

[Anonymous]

Hello 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?

[MFelix]

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

[Anonymous]

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

[MFelix]

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

[Anonymous]

Hello 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

[MFelix]

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

[parry2k]

@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.