Home Forums Power Pivot Accounts table with sum accounts, min max problem

This topic contains 8 replies, has 2 voices, and was last updated by  kingofhkb 3 months, 3 weeks ago.

Viewing 9 posts - 1 through 9 (of 9 total)
• Author
Posts
• #8995

kingofhkb
Participant
• Started: 2
• Replies: 5
• Total: 7

Hi

New to the forum and hoping that somebody can help me with my problem

The datamodel consists of three tables Accounts, SumAccounts and a Transaction table. The accounts table is related to the transaction table, and so far the SumAccounts table is not related to any of the other two tables.

The accounts table include all of the accounts this includes the sum accounts. The SumAccounts table specifies which accounts are to be summed up when selecting on of the sum accounts

I am trying to write a measure that no matter which account I choose, from the Accounts table it returns the correct value.

If not for the sum accounts I could get by with the following measure:

`Total : SUM(Amount) `
However if I choose one of the sum accounts this measure do not return anything, because there is not any transaction on a sum account.
My idea was to write two measures one for the regular accounts and one for the sum accounts and then select which one to use with an if statment.

I have created the following measures

`Choosenaccount := sum(AccountNum)`

``` SumAccountMin := CALCULATE(MIN(SumAccounts[FromAccount]),FILTER(SumAccounts,SumAccounts[Sum_Account] = Choosenaccount))```

`SumAccountMax := CALCULATE(MAX(SumAccounts[ToAccount]),FILTER(SumAccounts,SumAccounts[Sum_Account] = Choosenaccount))`

`SumAccountTotal := CALCULATE( [Total] , FILTER(ALL(Accounts), Accounts[AccountNum] >= [SumAccountMin] && Accounts[AccountNum] <= [SumAccountMax]))`

The Chooseaccount, SumAccountMin and SumAccountMax all return the desired answer, but the I just can’t get the last measure to return anything, any help will be greatly appreciated .

###### Attachments:
You must be logged in to view attached files.
#8998

tomallan
Keymaster
• Started: 9
• Replies: 2244
• Total: 2253

What version of Excel are you using and can you attach some sample data in a workbook?

#9008

kingofhkb
Participant
• Started: 2
• Replies: 5
• Total: 7

I am using Excel 2016 (16.0.7927.1024) 32-bit

And I have attached a sample workbook

###### Attachments:
You must be logged in to view attached files.
#9012

tomallan
Keymaster
• Started: 9
• Replies: 2244
• Total: 2253

Thank you for the attached workbook, that helped to understand your case.

This is what you are trying to achieve with your model:

(1) You choose an account number (AccountNum) from the Accounts table (or somewhere else that has not yet been identified).
(2) That chosen account number is used to filter the SumAccounts table where SumAccounts[Sum_Account] matches the chosen account number.
(3) From the filtered SumAccounts table, a range is identified with a starting point based on the minimum value in the FromAccount column
and with an ending point based on the maximum value of the ToAccount column.
(4) Then these minimum and maximum values are used to filter the Accounts table (on Accounts[AccountNum] ) to find the related sum in the Transactions table.

Please confirm if I am understanding correctly or not, and we will proceed from there.

Tom

#9013

kingofhkb
Participant
• Started: 2
• Replies: 5
• Total: 7

That is correct, however not all of the accounts on the Accounts table are SumAccounts I also want to be able to choose accounts which are not Sum accounts.

Regards

Christian

#9014

tomallan
Keymaster
• Started: 9
• Replies: 2244
• Total: 2253

And when you choose accounts that are not sum accounts, do you want to calculate their total by just using their related records in the transactions table?

#9025

kingofhkb
Participant
• Started: 2
• Replies: 5
• Total: 7

Yes that is exactly what I am looking to do

#9031

tomallan
Keymaster
• Started: 9
• Replies: 2244
• Total: 2253

Consider the use of calculated columns in the Accounts table to work towards your answer.

Please see the attached workbook and investigate the Accounts table.

For each row in the accounts table, the SumAccounts Min and the Sum Accounts Max are calculated in their own respective columns. Then, a third calculated column checks for the existence of a range from the SumAccounts table, and when a range exists, the formula in the third calculated column uses that range to find the “Total”. When a range does not exist, the formula calculates a “Total” based on an Account’s related child records.

Something to think about: There is a fourth calculated column that checks for overlaps between ranges derived from the SumAccounts table. Since overlaps suggest the possibility of transactions being counted more than once: depending on your use of “Total”, you may or may not want to reconsider your model design.

###### Attachments:
You must be logged in to view attached files.
#9056

kingofhkb
Participant
• Started: 2
• Replies: 5
• Total: 7

It works perfectly, thanks a lot Tom

I am aware of the overlaps, they are supposed to be there. They calculate Revenue, Gross profit, EBITDA, EBIT and stuff like that.

Thanks again

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic.