Combination of multiple excel file based on columns

[NadeemAhamed]

Dear Community

I have one excel file for Parent company data 

another excel file for child (Subsidiray Company) data

i want to mirge and create one dashboard for consolidated report. 

and another dashboard for Subsidiray Company

is it possible to mirge the data based on company column in both excel files 

Parent Company Data:

Subsidiray Company Data:

Expecting Result: 

Plan values based on Category, Division and Company (Sum of Subsidary Company) 

Calculated columns and Rows:

* Gross Profit Rate will be (Gross Profit /Sales) 

* Variance will be (Actual-Plan) 

* Achievement will be (Actual/Plan)

Thank you in advance 

[Anonymous · Accepted solution]

Hi @NadeemAhamed ,

Thanks @DataNinja777  for the quick reply. Please allow me to provide additional insights:

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) We can create a table.

Table = 
var _a=FILTER(SELECTCOLUMNS('Parent Company Data',"Company",[Company],"Division",[Division],"Category",[Category]),[Company]="A"&& [Division]="IT")
var _b=DATATABLE ( 
    "Company", STRING, "Division", STRING,"Category",STRING,
    {
        { "A", "IT","Gross Profit Rate" }
    }
)
RETURN UNION(_a,_b)

(3) We can create calculated columns.

Plan = 
var _a=CALCULATE(SUM('Parent Company Data'[Plan]),FILTER('Parent Company Data',[Company]=EARLIER('Table'[Company]) && [Division]=EARLIER('Table'[Division]) && [Category]=EARLIER('Table'[Category])))
var _b=CALCULATE(SUM('Subsidiray Company Data'[Plan]),FILTER('Subsidiray Company Data',[Company]=EARLIER('Table'[Company]) && [Division]=EARLIER('Table'[Division]) && [Category]=EARLIER('Table'[Category])))
var _c=CALCULATE(SUM('Subsidiray Company Data'[Plan]),FILTER('Subsidiray Company Data',[Company]=EARLIER('Table'[Company]) && [Division]=EARLIER('Table'[Division]) && [Category]="Sales"))
var _d=CALCULATE(SUM('Parent Company Data'[Plan]),FILTER('Parent Company Data',[Company]=EARLIER('Table'[Company]) && [Division]=EARLIER('Table'[Division]) && [Category]="Sales"))
var _e=CALCULATE(SUM('Subsidiray Company Data'[Plan]),FILTER('Subsidiray Company Data',[Company]=EARLIER('Table'[Company]) && [Division]=EARLIER('Table'[Division]) && [Category]="Gross Profit"))
var _f=CALCULATE(SUM('Parent Company Data'[Plan]),FILTER('Parent Company Data',[Company]=EARLIER('Table'[Company]) && [Division]=EARLIER('Table'[Division]) && [Category]="Gross Profit"))
var _g=_c+_d
var _h=_e+_f
RETURN IF([Category]="Gross Profit Rate",DIVIDE(_h,_g),_a+_b)

Actual = 
var _a=CALCULATE(SUM('Parent Company Data'[Actual]),FILTER('Parent Company Data',[Company]=EARLIER('Table'[Company]) && [Division]=EARLIER('Table'[Division]) && [Category]=EARLIER('Table'[Category])))
var _b=CALCULATE(SUM('Subsidiray Company Data'[Actual]),FILTER('Subsidiray Company Data',[Company]=EARLIER('Table'[Company]) && [Division]=EARLIER('Table'[Division]) && [Category]=EARLIER('Table'[Category])))
var _c=CALCULATE(SUM('Subsidiray Company Data'[Actual]),FILTER('Subsidiray Company Data',[Company]=EARLIER('Table'[Company]) && [Division]=EARLIER('Table'[Division]) && [Category]="Sales"))
var _d=CALCULATE(SUM('Parent Company Data'[Actual]),FILTER('Parent Company Data',[Company]=EARLIER('Table'[Company]) && [Division]=EARLIER('Table'[Division]) && [Category]="Sales"))
var _e=CALCULATE(SUM('Subsidiray Company Data'[Actual]),FILTER('Subsidiray Company Data',[Company]=EARLIER('Table'[Company]) && [Division]=EARLIER('Table'[Division]) && [Category]="Gross Profit"))
var _f=CALCULATE(SUM('Parent Company Data'[Actual]),FILTER('Parent Company Data',[Company]=EARLIER('Table'[Company]) && [Division]=EARLIER('Table'[Division]) && [Category]="Gross Profit"))
var _g=_c+_d
var _h=_e+_f
RETURN IF([Category]="Gross Profit Rate",DIVIDE(_h,_g),_a+_b)

Variance = [Actual]-[Plan]

Achievement = DIVIDE([Actual],[Plan])

(4) Then the result is as follows.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[DataNinja777]

Hi @NadeemAhamed ,

There is no need to combine your parent fact table and your subsidiary fact table in a combined fact tables in Power BI data model.  You can leave them as separate fact tables and create relationship with those fact tables and common dimension tables such as Company, Division, and Category dimension tables.  Also, I would unpivot your Actual and Plan fields so that you would not need to create separate measures for those two scenarios.  I tried to replicate the same result with your example above, but numbers didn't match with yours as I may be missing some information.  Also, it looks like your numbers are unrealistic as gross profit cannot be over 100% 😆.

[NadeemAhamed]

@DataNinja777 

Thank you for understanding and reply

Sorry for saying by using the above steps i can't achive result as shown below.