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
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
v-shex-msft
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
v-shex-msft
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.
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
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.