Design Advice: Accounting Consolidations Best Practices

[Anonymous]

Hi Power Bi Community,

I need some advice on how to best set up my data for use in Power Bi.

I currently have several table set up for each year (2014, 2015, 2016, etc.) which looks like the below:

I can easily pull this table from our system. I would like to be able to use this table for ease of adding new data (2019, etc.)

GL Number	Balance	GL Name	Program Number	Type
10	$                 10.00	Sample	1	Revenue
15	$                 25.00	Sample 2	1	Revenue
20	$               (15.00)	Sample 3	1	Expense
25	$                 25.00	Sample 4	2	Revenue
35	$               (30.00)	Sample 5	2	Expense
40	$                 50.00	Sample 6	3	Revenue

I want to build a table in Power Bi that aggregates the data - However I want to be able to pull all of the data (which would show net income by program) or filter by Revenue Types ( which would pull total revenue by program) or filter by Expense type (which would pull total expense by program). This is the goal:

Program Number	2014	2015	2016	2017	2018
1	20	X	X	X	X
2	-5	X	X	X	X
3	50	X	X	X	X

And if we filtered by Revenue it would instead show:

Program Number	2014	2015	2016	2017	2018
1	35	X	X	X	X
2	25	X	X	X	X
3	50	X	X	X	X

I would then use that table to show trends over time, etc.

Does this make sense/is this possible? I can build this directly in Excel with various Vlookups and refresh from that but would really like to get it into PowerBi instead if possible. Can anyone point me in the right direction or is this even possible?

Thanks in advance!

[v-danhe-msft · Accepted solution]

Hi @Anonymous,

Based on my test, you could refer to below steps:

Add custome columns in your row tables:

Append your tables:

Apply it and create measures:

2014 = CALCULATE(SUM(Append1[Balance]),FILTER('Append1','Append1'[Custom]=2014))
2015 = CALCULATE(SUM(Append1[Balance]),FILTER('Append1','Append1'[Custom]=2015))

Now you could create the visual:

You could also download the pbix file to have a view.

Regards,

Daniel He

[Anonymous]

This works great. Thanks so much!