cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

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

accountids.PNGExample 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 ? Smiley Happy

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support Team
Community Support Team

Re: Hierarchy Account Filters for General Ledger Table

Hi @danielhjorvar,

 

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 Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
2 REPLIES 2
Highlighted
Community Support Team
Community Support Team

Re: Hierarchy Account Filters for General Ledger Table

Hi @danielhjorvar,

 

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 Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |

Re: Hierarchy Account Filters for General Ledger Table

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