cancel
Showing results for
Did you mean:
Highlighted
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

## 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:

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.

Community Support

## Re: getting totals according to specified group/criteria

Hi @nikahafiz ,

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.

9 REPLIES 9
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

Please mark my post as solution, this will also help others.

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

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

## Re: getting totals according to specified group/criteria

Could you post a screenshot of your model?
Please mark my post as solution, this will also help others.

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

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:

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.

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

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:

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.

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

## Re: getting totals according to specified group/criteria

Hi @nikahafiz ,

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.

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

Announcements

#### Announcing the New Spanish Forum

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

#### MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

#### ‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors