January 28, 2018 at 3:42 pm #8995
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 .January 29, 2018 at 3:02 pm #8998
What version of Excel are you using and can you attach some sample data in a workbook?January 30, 2018 at 1:21 pm #9008
I am using Excel 2016 (16.0.7927.1024) 32-bit
And I have attached a sample workbookJanuary 30, 2018 at 6:15 pm #9012
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.
TomJanuary 30, 2018 at 8:00 pm #9013
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.
ChristianJanuary 30, 2018 at 8:24 pm #9014
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?January 31, 2018 at 7:17 am #9025
Yes that is exactly what I am looking to doJanuary 31, 2018 at 4:46 pm #9031
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.February 1, 2018 at 2:09 pm #9056
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.
You must be logged in to reply to this topic.