Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
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
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.
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
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
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |