DAX to filter values based on other tables

[Henceman]

Hi,

I would need advice on how to create pivottable, based on inputs from several tables.

1. First I have an account masterdata table

Account	Sign	Level	hierarcy
1010	1	Unit	Balance sheet
5610	-1	PG	P&L

This table defines characteristics of each account, and if the account data is needed to be detailed on product group level or can be summed ut to unit level


2. Second I have Balance sheet table, which roughly looks like this

	Unit1	Unit1	Unit2	Unit3
Account	4456	4457	5565	6652
1010	500	-56	0	-56
5610	300	23	1700	954

3. Third I have similar table for P&L values

	Unit1	Unit1	Unit2	Unit3
Account	4456	4457	5565	6652
1010	566	565	22	-0
5610	456	3	0	13

4. Fouth I have a table, which will list all the Units and their product groups, like that

Unit	PG
Unit1	4456
Unit1	4457
Unit2	5565
Unit3	6652

End goal?

Pivot, which will:

-be based on the account list table (meaning, that every account in the list must be present in the table, value or no value)
-Based on account setting, must pick correct value from correct BS or PL table (in this exmple, if account 1010 is balance sheet account, then it must ignore the account value in P&L table and pick the value from BS table)
-If its determined which table is correct, it must check if account value needs to be summed across the PG-s the unit has OR repeat the account for every PG there is in the list. Meaning, if account is Unit total, then it cannot display every unit PG individually
-Of course, account table specifies the sign, so if -1 is present, that account needs to be with opposite sign.


An Pivot example based on the tables below

Account	Unit	PG	Value
1010	Unit1		444
1010	Unit2		0
1010	Unit3		-56
5610	Unit1	4456	-456
5610	Unit1	4457	-3
5610	Unit2	5565	22
5610	Unit3	6652	-13

I hope my reasoning was understandable and doable with DAX, with so many filters...

 

[Henceman]

I created an example here

https://easyupload.io/ve0fq6