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 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:
'G_L'[G_L Account No_] >= 10005 &&
'G_L'[G_L Account No_] <= 10190;
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 ? 🙂
Go to Solution.
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:
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])
View solution in original post
Check out the on demand sessions that are available now!
Check out the Winners!
Features releasing from October 2020 through March 2021