cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
misunika95
Frequent Visitor

DAX statement with Ifs and nested SUMIFS

Hi!

 

So I have a table with acccounts receivable info per account that has the following columns:

 

  • group: Credit/Cash
  • accountNumber
  • debtUSD
  • invoiceType
  • collectionsAgent
  • timeInterval

I'm trying to clasify their type fo debt based on several conditions. I had previously calculated this in the same Excel table with this formula:

 

=IF(IF([@group]<>"CASH",SUMIF([accountNumber],[@accountNumber],[debtUSD]))<0,"Credit Balance",IF(AND([@[debtUSD]]<0,OR([@[invoiceType]]="AA",[@[invoiceType]]="BB",[@[invoiceType]]="CC",[@[invoiceType]]="DD",[@[invoiceType]]="EE",[@[invoiceType]]="WV")),"Unapplied Payment",IF(OR([@[collectionsAgent]]="JohnDoe"),"Legal",[@[timeInterval]])))

 

In summary, the formula will calculate and classify as "Credit Balance", "Unapplied Payment" or "Legal" depending on the group, the amount of debt and the invoice type. Else, it will return the aging of the debt previously calculated on the column timeInterval (if it's 1-30 days old, 31-60, etc). 

 

I was wondering if there is an equivalent expression in DAX I can use. I would really appreciate the help guys 🙂

1 ACCEPTED SOLUTION
Anonymous
Not applicable

OK.

 

Classification = 
var __currentAccountNumber = 'Accounts Receivable'[Account Number]
var __currentDebt = 'Accounts Receivable'[Debt USD]
var __currentInvoiceType = 'Accounts Receivable'[Invoice Type]
var __currentTimeInterval = 'Accounts Receivable'[Time Interval]
var __sumOfDebtForCurrentClient = 
    SUMX(
        FILTER(
            'Accounts Receivable',
            'Accounts Receivable'[Account Number] = __currentAccountNumber
        ),
        'Accounts Receivable'[Debt USD]
    )
var __classification =
    SWITCH( TRUE(),

        __sumOfDebtForCurrentClient < 0, 
            "Credit Balance",
            
        __currentDebt < 0 && __currentInvoiceType in {"AA", "BB", "CC", "DD"},
            "Unapplied Payment",

        // else
        __currentTimeInterval
    )
return
    __classification

This is your formula for the Classification Column in your table.

 

If this is not performant enough, then here's a variation on this topic. Just change these lines:

 

var __sumOfDebtForCurrentClient = 
    SUMX(
        FILTER(
            'Accounts Receivable',
            'Accounts Receivable'[Account Number] = __currentAccountNumber
        ),
        'Accounts Receivable'[Debt USD]
    )

to these

 

var __sumOfDebtForCurrentClient =
    CALCULATE(
        SUM( 'Accounts Receivable'[Debt USD] ),
        ALLEXCEPT( 
            'Accounts Receivable',
            'Accounts Receivable'[Account Number] 
        )
    )

Not sure which one would be faster on a big table.

 

By the way, here's the Excel formula (which seems to implement a different logic than the one you've described), courtesy of http://excelformulabeautifier.com/:

=IF(
    IF(
        [@group] <> "CASH",
        SUMIF(
            [accountNumber],
            [@accountNumber],
            [debtUSD]
        )
    ) < 0,
    "Credit Balance",
    IF(
        AND(
            [@[debtUSD]] < 0,
            OR(
                [@[invoiceType]] = "AA",
                [@[invoiceType]] = "BB",
                [@[invoiceType]] = "CC",
                [@[invoiceType]] = "DD",
                [@[invoiceType]] = "EE",
                [@[invoiceType]] = "WV"
            )
        ),
        "Unapplied Payment",
        IF(
            OR(
                [@[collectionsAgent]] = "JohnDoe"
            ),
            "Legal",
            [@[timeInterval]]
        )
    )
)

But I've implemented the logic you've described 🙂

 

Best

Darek

View solution in original post

9 REPLIES 9
Anonymous
Not applicable

By the way, please disentangle the logic of the Excel formula. I don't think anyone would want to do it themselves - too much work. You have to state the algorithm in a clear, unambiguous way so that the rules for calculation are easily understood. Then and only then will people start working on this 🙂

 

Best

Darek

Okay so basically the table returns all the invoices the company has in accounts receivable, which means that one accountNumber can have several invoices. Taking that into account the pseudo code would be something like this

 

If the sum of the debt per client < 0 (SumIf in the excel formula above to sum all the debt from the same customer)

Then client has a "credit balance"

Else if debtUSD < 0 and document type is AA or BB or CC or DD

Then client has "Unapplied Payment"

Else return same value as the column timeInterval

 

I hope this is helpful and thanks for the tip about Switch function

Anonymous
Not applicable

OK.

 

Classification = 
var __currentAccountNumber = 'Accounts Receivable'[Account Number]
var __currentDebt = 'Accounts Receivable'[Debt USD]
var __currentInvoiceType = 'Accounts Receivable'[Invoice Type]
var __currentTimeInterval = 'Accounts Receivable'[Time Interval]
var __sumOfDebtForCurrentClient = 
    SUMX(
        FILTER(
            'Accounts Receivable',
            'Accounts Receivable'[Account Number] = __currentAccountNumber
        ),
        'Accounts Receivable'[Debt USD]
    )
var __classification =
    SWITCH( TRUE(),

        __sumOfDebtForCurrentClient < 0, 
            "Credit Balance",
            
        __currentDebt < 0 && __currentInvoiceType in {"AA", "BB", "CC", "DD"},
            "Unapplied Payment",

        // else
        __currentTimeInterval
    )
return
    __classification

This is your formula for the Classification Column in your table.

 

If this is not performant enough, then here's a variation on this topic. Just change these lines:

 

var __sumOfDebtForCurrentClient = 
    SUMX(
        FILTER(
            'Accounts Receivable',
            'Accounts Receivable'[Account Number] = __currentAccountNumber
        ),
        'Accounts Receivable'[Debt USD]
    )

to these

 

var __sumOfDebtForCurrentClient =
    CALCULATE(
        SUM( 'Accounts Receivable'[Debt USD] ),
        ALLEXCEPT( 
            'Accounts Receivable',
            'Accounts Receivable'[Account Number] 
        )
    )

Not sure which one would be faster on a big table.

 

By the way, here's the Excel formula (which seems to implement a different logic than the one you've described), courtesy of http://excelformulabeautifier.com/:

=IF(
    IF(
        [@group] <> "CASH",
        SUMIF(
            [accountNumber],
            [@accountNumber],
            [debtUSD]
        )
    ) < 0,
    "Credit Balance",
    IF(
        AND(
            [@[debtUSD]] < 0,
            OR(
                [@[invoiceType]] = "AA",
                [@[invoiceType]] = "BB",
                [@[invoiceType]] = "CC",
                [@[invoiceType]] = "DD",
                [@[invoiceType]] = "EE",
                [@[invoiceType]] = "WV"
            )
        ),
        "Unapplied Payment",
        IF(
            OR(
                [@[collectionsAgent]] = "JohnDoe"
            ),
            "Legal",
            [@[timeInterval]]
        )
    )
)

But I've implemented the logic you've described 🙂

 

Best

Darek

View solution in original post

Hello Darek, just a quick follow up:

 

Power Bi is giving me an issue with the declared variables with the following error:

 

A single value for column 'Account Number' in table 'Accounts Receivable' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

 

Same thing happens with the other variables. Do you happen to know any fix for this?

 

Regards

Anonymous
Not applicable

I did say: This is your formula for the Classification column in your table. This is not a measure.

 

Are you trying to use any of the formulas as measures? If you do, then you'll get this error.

 

One last thing: READ WHAT I'VE WRITTEN BEFORE WELL AND TRY TO UNDERSTAND IT. Then you'll have no problems. When you read things, read them carefully with a full understanding. Don't glance over text. READ UNTIL YOU FULLY UNDERSTAND WHAT'S BEEN EXPRESSED IN THERE.

 

Best

Darek

Hi Darek!

 

Yeah I see the mistake. When I was trying to add it as a column it returned the error "Token Eof expected". But I was trying to add the column from the query editor rather than with the Add column button in the ribbon tab, in my mind they were the same thing.

 

Thanks again now it's fully working.

 

Regards

Anonymous
Not applicable

Good 🙂

 

Best
Darek

It's working fine! Both sumx and switch formulas were really helpful and that excel formula beautifier will be helpful.

 

Thankyou,

MS

Anonymous
Not applicable

See this:

 

SWITCH in DAX

 

Best

Darek

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors