If # is within a range classify as X

[BrendanJA]

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.

[danextian · Accepted solution]

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.

[BrendanJA]

Thank you, both functions work 👍