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 6 months, 2 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: 2377
    • Total: 2386

    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: 2377
    • Total: 2386

    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: 2377
    • Total: 2386

    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: 2377
    • Total: 2386

    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.