Hierarchy Account Filters for General Ledger Table

[Anonymous]

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 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 ? 🙂

[v-shex-msft · Accepted solution]

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:

 

Detail table:

 

Measure:

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

Other way, use relationship:
 
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.
 

Slicer.
 

Result.
 

Regards,
Xiaoxin Sheng

[Anonymous]

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