Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
hi,
i'm doing a financial report that uses charts of accounts (with numerous account codes / account names) such as
account code account names account type
10000-10999 cash assets
11000-11999 account receivables assets
...
20000-20999 account payable liabilities
21000-21999 other creditors liabilities
...
30000-30999 rent income income
31000-31999 book sales income
....
40000-40999 utility expense expense
41000-41999 salary expense expense
note:
there are more subcodes for different account names under each account code range.
kindly advise on how to sum up the value/amount for each account code that will be put under each account code range.
subsequenly, we need to also sum up according to assets, liabilities, expenses, & revenue.
for info, i'm doing this for 50+ organizations that are clustered according to geographical zones (e.g. north, south, east, west) that will eventually also be summed up at the geographical zones and finally as 1 consolidated financial report.
tks, -nik
Solved! Go to Solution.
Hi @Anonymous ,
If i understand you correctly, you can use ORG_code or Detail type as a filtering parameter.
Please check below measure and see if the result achieve your expectation.
Measure = CALCULATE(SUM(ORG[Amount]),FILTER(ALL(ORG),ORG[ORG_code] = MAX(ORG[ORG_code])))
Result would be shown as below:
BTW, Pbix as attached, hopefully works for you.
Best Regards,
Jay
Community Support Team _ Jay Wang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
Thanks for your information.
According to your relationship diagram, the amount column is in org-finance table. Is the account_code in org-finance table only cantains sub-account(like 10010,10020,20010,20020)? And what is org-code? I thought it was parent-account like 10000, 20000 before.
Anyway, please check below measure and see if the result is what you want (Although I don't think the logic is rigorous).
Measure = CALCULATE(SUM(ORG[Amount]),FILTER(ALL(Account),MID(Account[Account No.],1,2) = MID(MAX(Account[Account No.]),1,2)))
Best Regards,
Jay
Community Support Team _ Jay Wang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
Please check following measures and see if the result achieve your expectation:
group by names = CALCULATE(SUM('Table'[amount]),FILTER(ALL('Table'),'Table'[ account names] = MAX('Table'[ account names])))
group by types = CALCULATE(SUM('Table'[amount]),FILTER(ALL('Table'),'Table'[account type] = MAX('Table'[account type])))
Result would be shown as below:
If I misunderstood your meaning, please share some sample data and expected result to me if you don't have any Confidential Information.
Best Regards,
Jay
Community Support Team _ Jay Wang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
thanks for your responses, @mwegener & @v-jayw-msft.
appended below is a sample of the accounts (or chart of accounts) that i'm working on (of course, there are actually more accounts than the ones listed below but these should suffice for now here):
Account No. | Account | Detail type |
10000 | Cash | Current Assets |
10010 | Cash - Org 1 | Current Assets |
10020 | Cash - Org 2 | Current Assets |
10030 | Cash - Org 3 | Current Assets |
10040 | Cash - Org 4 | Current Assets |
10050 | Cash - Org 5 | Current Assets |
10060 | Cash - Org 6 | Current Assets |
10070 | Cash - Org 7 | Current Assets |
10080 | Cash - Org 8 | Current Assets |
10090 | Cash - Org 9 | Current Assets |
10100 | Cash - Org 10 | Current Assets |
Account No. | Account | Detail type |
20000 | Account Payables | Current Liabilities |
20010 | Account Payables - Org 1 | Current Liabilities |
20020 | Account Payables - Org 2 | Current Liabilities |
20030 | Account Payables - Org 3 | Current Liabilities |
20040 | Account Payables - Org 4 | Current Liabilities |
20050 | Account Payables - Org 5 | Current Liabilities |
20060 | Account Payables - Org 6 | Current Liabilities |
20070 | Account Payables - Org 7 | Current Liabilities |
20080 | Account Payables - Org 8 | Current Liabilities |
20090 | Account Payables - Org 9 | Current Liabilities |
20100 | Account Payables - Org 10 | Current Liabilities |
Account No. | Account | Detail type |
13000 | Rent Income | Income |
13010 | Rent Income - Org 1 | Income |
13020 | Rent Income - Org 2 | Income |
13030 | Rent Income - Org 3 | Income |
13040 | Rent Income - Org 4 | Income |
13050 | Rent Income - Org 5 | Income |
13060 | Rent Income - Org 6 | Income |
13070 | Rent Income - Org 7 | Income |
13080 | Rent Income - Org 8 | Income |
13090 | Rent Income - Org 9 | Income |
13100 | Rent Income - Org 10 | Income |
Account No. | Account | Detail type |
15000 | Utility Expense | Expense |
15010 | Utility Expense - Org 1 | Expense |
15020 | Utility Expense - Org 2 | Expense |
15030 | Utility Expense - Org 3 | Expense |
15040 | Utility Expense - Org 4 | Expense |
15050 | Utility Expense - Org 5 | Expense |
15060 | Utility Expense - Org 6 | Expense |
15070 | Utility Expense - Org 7 | Expense |
15080 | Utility Expense - Org 8 | Expense |
15090 | Utility Expense - Org 9 | Expense |
15100 | Utility Expense - Org 10 | Expense |
if we start with the cash account (account no: 10000), i'd appreciate help in getting dax formula(s) to sum up the cash accounts from org #1 to org #10 (cash account no's: 10010, 10020, 10030, 10040, 10050, 10060, 10070. 10080, 10090, 10100).
i believe the same dax formula(s) may be applied to other accounts / account types (e.g. account no: 20000 for account payables, account no: 13000 for rent income, or account no: 15000 for utility expense) to get further aggregated accounting numbers later.
i'm also appending below the relationship diagram for the data model that i'm working on:
tks & krgds, -nik
Hi @Anonymous ,
If i understand you correctly, you can use ORG_code or Detail type as a filtering parameter.
Please check below measure and see if the result achieve your expectation.
Measure = CALCULATE(SUM(ORG[Amount]),FILTER(ALL(ORG),ORG[ORG_code] = MAX(ORG[ORG_code])))
Result would be shown as below:
BTW, Pbix as attached, hopefully works for you.
Best Regards,
Jay
Community Support Team _ Jay Wang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
tks again, @v-jayw-msft.
my mistake, the grouping should be with the account codes i.e. group account code 10000 should consist of account codes between 10001-10999, account group code 20000 is for 20001-20999, account group code 30000 is for 30001-30999 & so on. the grouping & summation will be by such group account codes?
your grouping measure is for in ORG table but what i need is a grouping dax measure in the Account table.
tks, -nik
Hi @Anonymous ,
Thanks for your information.
According to your relationship diagram, the amount column is in org-finance table. Is the account_code in org-finance table only cantains sub-account(like 10010,10020,20010,20020)? And what is org-code? I thought it was parent-account like 10000, 20000 before.
Anyway, please check below measure and see if the result is what you want (Although I don't think the logic is rigorous).
Measure = CALCULATE(SUM(ORG[Amount]),FILTER(ALL(Account),MID(Account[Account No.],1,2) = MID(MAX(Account[Account No.]),1,2)))
Best Regards,
Jay
Community Support Team _ Jay Wang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
many tks for the guide, jay.
[i'm sorry for the late response too as i have been travelling & tied down with some urgent matters].
i think the table naming is quite confusing & i'm sorry for that again. i shall take a look your suggested solution shortly.
krgds, -nik
Hi @Anonymous ,
use the concept of star-schema
https://docs.microsoft.com/en-us/power-bi/guidance/star-schema
and create a dimension table account with your attributes and hierarchy struktur
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.
tks, @mwegener.
while i have already set up my data model using the star-schema, i'm actually looking for dax formula to grouping & summation for the account ranges/codes as i requested earlier. i hope i can get a guidance on that (i believe it's related to var function).
krgds, -nik
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
117 | |
101 | |
71 | |
61 |