Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
juanso14
Helper I
Helper I

Calculate Sales for parent and Child

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)SalesSales by Subsidiary (%)Sales by Subsidiary $
AAAAAparent company $              500.00030% $                       150.000
AAAAACCCC 10% $                          50.000
AAAAADDDD 10% $                          50.000
AAAAAEEEE 35% $                       175.000
AAAAAFFFF 10% $                          50.000
AAAAAGGGG 5% $                          25.000
BBBBBBparent company $        45.880.02030% $                 13.764.006
BBBBBBCCCC 10% $                    4.588.002
BBBBBBDDDD 10% $                    4.588.002
BBBBBBEEEE 10% $                    4.588.002
BBBBBBFFFF 20% $                    9.176.004
BBBBBBGGGG 20% $                    9.176.004
CCCCCparent company $     986.335.53525% $               246.583.884
CCCCCCCCC 50% $               493.167.768
CCCCCDDDD 15% $               147.950.330
CCCCCEEEE 10% $                 98.633.554
   $  1.032.715.555 $                                         3 $           1.032.715.555

 

Thanks 
Juan Vido

5 REPLIES 5
v-piga-msft
Resident Rockstar
Resident Rockstar

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:

 Capture.png

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

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.

tablas.JPG

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. 

 

Tablero.JPG

 

Thanks

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:

 

tablero 3.JPG

I attach a pbix example. PBIX

 

Thanks

Best Regards

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

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.