Forum Discussion

BrendanJA's avatar
BrendanJA
New Member
2 years ago
Solved

If # is within a range classify as X

Good afternoon,

 

I have a chart of accounts and I want to further categorize them to summarize the data. Is there a custom column formula I could create that would work like so;

 

1000-1999 = Asset

2000-2999= Liability

3000-3999 = Equity

4000-4999 = Revenue

5000-5999 = Expense

 

I know how to simply do this in excel with an if statement, but with the connecter I have with PowerBI to my accounting software I can not export from powerBI to excel and bring it back without breaking the real-time connector.

Need Help
  • danextian's avatar
    danextian
    2 years ago

    Hi BrendanJA ,

     

    There are many ways to do this.

    =
SWITCH (
    TRUE (),
    [number] <= 1999, "Asset",
    [number] <= 2999, "Liability",
    [number] <= 3999, "Equity",
    [number] <= 4999, "Revenue",
    [number] <= 5999, "Expense"
)

     

    =
SWITCH (
    VALUE ( LEFT ( [number], 1 ) ),
    1, "Asset",
    2, "Liability",
    3, "Equity",
    4, "Revenue",
    5, "Expense"
)

     

    They are to be entered as a calc column. Replace [number] with the actual table and column name. The second one just checks for the first digit.

2 Replies

  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    2 years ago

    Hi BrendanJA ,

     

    There are many ways to do this.

    =
SWITCH (
    TRUE (),
    [number] <= 1999, "Asset",
    [number] <= 2999, "Liability",
    [number] <= 3999, "Equity",
    [number] <= 4999, "Revenue",
    [number] <= 5999, "Expense"
)

     

    =
SWITCH (
    VALUE ( LEFT ( [number], 1 ) ),
    1, "Asset",
    2, "Liability",
    3, "Equity",
    4, "Revenue",
    5, "Expense"
)

     

    They are to be entered as a calc column. Replace [number] with the actual table and column name. The second one just checks for the first digit.