Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

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
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.