cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nikahafiz Resolver I
Resolver I

getting totals according to specified group/criteria

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

2 ACCEPTED SOLUTIONS

Accepted Solutions
Community Support
Community Support

Re: getting totals according to specified group/criteria

Hi @nikahafiz ,

 

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:

2.PNG 

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.

View solution in original post

Community Support
Community Support

Re: getting totals according to specified group/criteria

Hi @nikahafiz ,

 

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)))

3.PNG

 

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.

View solution in original post

9 REPLIES 9
Super User III
Super User III

Re: getting totals according to specified group/criteria

Hi @nikahafiz ,

 

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.

 

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

I work at KUMAVISION AG , one of the world's largest
implementation partners for Microsoft Dynamics. #BetterTogether

nikahafiz Resolver I
Resolver I

Re: getting totals according to specified group/criteria

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

Super User III
Super User III

Re: getting totals according to specified group/criteria

Could you post a screenshot of your model?
Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

I work at KUMAVISION AG , one of the world's largest
implementation partners for Microsoft Dynamics. #BetterTogether

Highlighted
Community Support
Community Support

Re: getting totals according to specified group/criteria

Hi @nikahafiz ,

 

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:

1.PNG

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.

nikahafiz Resolver I
Resolver I

Re: getting totals according to specified group/criteria

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.AccountDetail type
10000CashCurrent Assets
10010Cash - Org 1Current Assets
10020Cash - Org 2Current Assets
10030Cash - Org 3Current Assets
10040Cash - Org 4Current Assets
10050Cash - Org 5Current Assets
10060Cash - Org 6Current Assets
10070Cash - Org 7Current Assets
10080Cash - Org 8Current Assets
10090Cash - Org 9Current Assets
10100Cash - Org 10Current Assets

 

Account No.AccountDetail type
20000Account PayablesCurrent Liabilities
20010Account Payables - Org 1Current Liabilities
20020Account Payables - Org 2Current Liabilities
20030Account Payables - Org 3Current Liabilities
20040Account Payables - Org 4Current Liabilities
20050Account Payables - Org 5Current Liabilities
20060Account Payables - Org 6Current Liabilities
20070Account Payables - Org 7Current Liabilities
20080Account Payables - Org 8Current Liabilities
20090Account Payables - Org 9Current Liabilities
20100Account Payables - Org 10Current Liabilities

 

Account No.AccountDetail type
13000Rent IncomeIncome
13010Rent Income - Org 1Income
13020Rent Income - Org 2Income
13030Rent Income - Org 3Income
13040Rent Income - Org 4Income
13050Rent Income - Org 5Income
13060Rent Income - Org 6Income
13070Rent Income - Org 7Income
13080Rent Income - Org 8Income
13090Rent Income - Org 9Income
13100Rent Income - Org 10Income

 

Account No.AccountDetail type
15000Utility ExpenseExpense
15010Utility Expense - Org 1Expense
15020Utility Expense - Org 2Expense
15030Utility Expense - Org 3Expense
15040Utility Expense - Org 4Expense
15050Utility Expense - Org 5Expense
15060Utility Expense - Org 6Expense
15070Utility Expense - Org 7Expense
15080Utility Expense - Org 8Expense
15090Utility Expense - Org 9Expense
15100Utility Expense - Org 10Expense

 

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:

 

relationship.JPG

 

tks & krgds, -nik

Community Support
Community Support

Re: getting totals according to specified group/criteria

Hi @nikahafiz ,

 

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:

2.PNG 

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.

View solution in original post

nikahafiz Resolver I
Resolver I

Re: getting totals according to specified group/criteria

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

Community Support
Community Support

Re: getting totals according to specified group/criteria

Hi @nikahafiz ,

 

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)))

3.PNG

 

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.

View solution in original post

nikahafiz Resolver I
Resolver I

Re: getting totals according to specified group/criteria

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

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors