cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Hierarchy Account Filters for General Ledger Table

Dear PBI users...

 

So I have about 250 unique AccountIDs and each of them is marked from 1-4 (based on what the account is for).

 

50 of these accounts are made up of the others. That is, 50 accounts add up other sub-accounts. From the SQL DB query I have one other column that shows what sub-accounts add up the TotalAccount. 

 

Example

Example of the Account Table setupExample of the Account Table setupThis means that 10005 & 10190 and everything in between the sub-accounts 10005 to 10190 add up KeyAccount "Total Revenue".

 

I have around 50 KeyAccounts that all have these "Totaling" prescriptions. 

 

What I want is to be able to slice the General Ledger as I want, using the KeyAccounts

 

I did create a column in the ledger table which checks if the current line belongs to the column I'm trying to filter, like this:

TotalRevenue 
              = SWITCH(
                     TRUE();
                         'G_L'[G_L Account No_] >= 10005 && 
                         'G_L'[G_L Account No_] <= 10190;
                "TotalRevenue";
              BLANK()
            )

It works BUT this is a very limited solution however...

 

One solution is to manually create 50 columns and name them after each KeyAccount, then create Hierarchy and use that as a slicer but that seems a bit far fetched and much work. 

 

I've searched for Parent-Child Hierarchies and googled my way to understanding the PATH functions but I'm kind of stuck on this one.

 

So.... ehm..... anyone ? 🙂

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support
Community Support

Hi @Anonymous,

 

According to your description, you want to use key account to get the subaccount and the sub total amount, right?

If as I said, you can refer to below steps to get the sub account and the sub total:

 

Tables.
Parent table:

 

Capture.PNG 

 

Detail table:

Capture2.PNG
 

Measure:

Sub Accounts =
CONCATENATEX(FILTER(Sheet2,Sheet2[Parent]=MAX(Sheet1[ID])),[Account ID]&",")

 

Other way, use relationship:
 Capture3.PNG
Sub Accounts 2 = CONCATENATEX(FILTER(Sheet2,Sheet2[Parent]=RELATED(Sheet1[ID])),[Account ID]&",")

 

Subtotal = SUMX( FILTER(Sheet2,Sheet2[Parent]=MAX(Sheet1[ID])),[Amount])

 

Create visuals:

Table visual.
Capture4.PNG 

 

Slicer.
Capture5.PNG 

 

Result.
Capture6.PNG 

 

Regards,
Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

2 REPLIES 2
Community Support
Community Support

Hi @Anonymous,

 

According to your description, you want to use key account to get the subaccount and the sub total amount, right?

If as I said, you can refer to below steps to get the sub account and the sub total:

 

Tables.
Parent table:

 

Capture.PNG 

 

Detail table:

Capture2.PNG
 

Measure:

Sub Accounts =
CONCATENATEX(FILTER(Sheet2,Sheet2[Parent]=MAX(Sheet1[ID])),[Account ID]&",")

 

Other way, use relationship:
 Capture3.PNG
Sub Accounts 2 = CONCATENATEX(FILTER(Sheet2,Sheet2[Parent]=RELATED(Sheet1[ID])),[Account ID]&",")

 

Subtotal = SUMX( FILTER(Sheet2,Sheet2[Parent]=MAX(Sheet1[ID])),[Amount])

 

Create visuals:

Table visual.
Capture4.PNG 

 

Slicer.
Capture5.PNG 

 

Result.
Capture6.PNG 

 

Regards,
Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

Anonymous
Not applicable

Xiaoxin! Super thanks for this! Been working with your solution and built on that and it seems to be working really well 🙂

Helpful resources

Announcements
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors