Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
This 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 ? 🙂
Solved! Go to 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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
93 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |