Calculate Sales for parent and Child

[juanso14]

Hello, im working in Power BI with dinamics 365, and i have some accounts that are parent from another. I have the total sales for the parent account and the % of sales of the child for the total of the parents (the % is on the account table) and the sales is in another table.

An example is like this, whre i wont the sales by subsidiary. 

Company (parent)	Subsidiary (child)	Sales	Sales by Subsidiary (%)	Sales by Subsidiary $
AAAAA	parent company	$              500.000	30%	$                       150.000
AAAAA	CCCC		10%	$                          50.000
AAAAA	DDDD		10%	$                          50.000
AAAAA	EEEE		35%	$                       175.000
AAAAA	FFFF		10%	$                          50.000
AAAAA	GGGG		5%	$                          25.000
BBBBBB	parent company	$        45.880.020	30%	$                 13.764.006
BBBBBB	CCCC		10%	$                    4.588.002
BBBBBB	DDDD		10%	$                    4.588.002
BBBBBB	EEEE		10%	$                    4.588.002
BBBBBB	FFFF		20%	$                    9.176.004
BBBBBB	GGGG		20%	$                    9.176.004
CCCCC	parent company	$     986.335.535	25%	$               246.583.884
CCCCC	CCCC		50%	$               493.167.768
CCCCC	DDDD		15%	$               147.950.330
CCCCC	EEEE		10%	$                 98.633.554
		$  1.032.715.555	$                                         3	$           1.032.715.555

Thanks 
Juan Vido

[v-piga-msft]

Hi juanso14,

If  I  understand your scenario correctly that you have two tables which have relationships, the first table contains the column Company(parent), column Subsidiary(child) and Sales by Subsidiary(%). The another table contains the Sales and the column Company(parent). Then you could create a relationship of the two table and create a column Sales by Subsidiary $, you could refer to this formula :

Sales by Subsidiary $= 'Sheet1'[Sales by Subsidiary (%)]*RELATED(Sheet2[Sales])

The result should be the visual below:

 

If I misunderstand your scenario, I would appreciate it if you could provide your sample. Hope it can help you.

Best Regards,

Cherry

[juanso14]

Hello, thank you for the answer, but I have all the information of parent and child in one table, with id or by name, also i have the % of the subsidiary, i only have the sales information in another table.  I cant send you the model because is confidential 

But i Have to make reference from the parent. 

Here i copy the columns i have.

[juanso14]

Look, for example, I have this data:

The name of the account the id of the account the id for the parrent (that is the relantionship) the % of sales by subsidiary. But the problem is that in some cases I have sales that dont have a subsidiary. And it have to have sales data. 

Thanks

[juanso14]

Hello @v-piga-msft thank you for the response, i made the following formula:

Ventas Calculado x %2 =
VAR current_account =
SELECTEDVALUE ( Cuentas[accountid] )
VAR _parent =
CALCULATETABLE (
VALUES ( Cuentas[_parentaccountid_value] ),
FILTER ( ALL ( Cuentas), [accountid] = current_account )
)
RETURN
CALCULATE (
SUM ( 'Ventas 17-18'[Ventas Total USD] ),
FILTER ( ALL ( Cuentas ), [accountid] IN _parent )
)

But the results were not ok. Look:

I attach a pbix example. PBIX

Thanks

Best Regards

[v-piga-msft]

Hi @juanso14,

Sorry for the delay response. It seems that your PBIX has expired，I would appreciate it if you could reupload it.

Best Regards, 

Cherry