Multi-company P&L report with Power BI.

[DRnotstrange]

Hello everyone,

I have PowerBI report (not created by me, just inherited) that I need to optimise memory consuption. Report consist of more number of companies. Each company can have different ERP (5 different ERPs in group), and different source SQL databse, Sharepoint, Excel etc.

Report is created with this logic. Each company have this kind of DAX measure that react on user slicer in report:

PL_companyxyz = 
VAR _PL1 = SUM([PL1])
VAR _PL2 = SUM([PL2])
VAR _PL3 = SUM([PL3])
VAR _PL4 = SUM([PL4])

RETURN
SWITCH( SELECTEDVALUE(Slicer_data[data1]) ,
    "PL1" , _PL1 ,
    "PL2" , _PL2,
    "PL3", _PL3,
    "PL4" , _PL4 , _PL1)

Each PL column contains the type of accounting entries.

In the report there is a consolidation DAX that binds each company's data together:

PL_all_companies = 

var XYZ =  IF( CONTAINS(Group , Group[Company] , "XYZ" ) , [PL_companyxyz] , BLANK()) 
var XYZ2 =  IF( CONTAINS(Group , Group[Company] , "XYZ2" ) , [PL_companyxyz2] , BLANK()) 
var XYZ3 =  IF( CONTAINS(Group , Group[Company] , "XYZ3" ) , [PL_companyxyz3] , BLANK())......etc

var all_companies =  XYZ + XYZ2 + XYZ3 + ..... etc

RETURN

all_companies

If statement is controlled by another slicer on page.

Is there a better way or best practice how to bind together all companies data, using DAX?

Thanks in advance,

Kind regards.

[Zang_Mi]

Hello,

If it is possible, try to unify data from all the companies into one data table to void calculating the total summing results from different measures.

Regards

[DRnotstrange]

Hello,

Thank you for your reply. I cannot do that because there are 5 different ERPs in group. I may be able to put them in ERP groups, but I would prefer the DAX solution if it is possible to write it in a better or more effective way.

Kind regards.