How to get a amount from all "child" of a field account in power bi?

[Lucivan_B]

Explaining the problem:

I have a table with the accounts and the total value, this is the "fact" table

Account	Valor
CC1035	-19000
CC1284	123400
CC1909	200
CC1024	3500

I also have a table with "account", at first I had only account and parent (almost like a graph). I used a method to get the recursive parents and put in levels: 

Parent	Account	Level1	Level2	Level3	Level4
	CC_Shared	CC_Shared
CC_Shared	CC_Adm	CC_Shared	CC_Adm
CC_Adm	CC1035	CC_Shared	CC_Adm	CC1035
CC_Adm	CC1284	CC_Shared	CC_Adm	CC1284
CC_Adm	CC1909	CC_Shared	CC_Adm	CC1909
	CC_Report	CC_Report
CC_Report	RCC	CC_Report	RCC
RCC	CC_CSC_Sold	CC_Report	RCC	CC_CSC_Sold
CC_CSC_Sold	CC1909	CC_Report	RCC	CC_CSC_Sold	CC1909
CC_CSC_Sold	CC1024	CC_Report	RCC	CC_CSC_Sold	CC1024

Obs: account could be in different levels and in differents branches (method path doesn't work), for example:

CC_Report -> RCC -> CC_CSC_Sold -> CC1909

CC_Shared -> CC_Adm -> CC1909

I want a slicer, for example, using "CC_Adm" and returning the total amount, in this case would be:

-19000 + 123400 + 200 = 104600

The problem: I would like a way to get any account and get all child below this account and create an Income statement unsing level (1,2,3,4) hierarchy to show all below.

Any ideias using concatenate or something else. Thank you all.

I tried using concatenate and a measure, but always fail using the slicer.

[Anonymous · Accepted solution]

Hi @Lucivan_B ,

I use an unconnected table and a visual filter, please check if this is your expected output.

The pbix file is attached for your reference.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[suparnababu8]

Hi @Lucivan_B 

If you want to achieve you goal, pls follow below steps

1. Create a Parent and chaild Hirearchy
2. Create a new calculated columns path by using this - (Path = PATH(Accounts[Account], Accounts[Parent]))
3. Then create a mesure to calculate total value by using below measure
   [[Total Value =
   VAR SelectedAccount = SELECTEDVALUE(Accounts[Account])
   VAR AllDescendants =
   CALCULATETABLE(
   Accounts,
   PATHCONTAINS(Accounts[Path], SelectedAccount)
   )
   RETURN
   SUMX(AllDescendants, Accounts[Valor])]]
4. Add a slocer and drag the Account field from your accounts table.This slicer will allow you to select an account.
5. Drag above total value measure into a new card.
6. Create an income statemnet using hierarchy levels, you can use a matrix visual. Add level1,level2,...to to rows and total value measure to vlaues.

Let me know if it works. Thanks