Need help using SUMX with nested IFs, getting Could not load data for visual

[ZIggyH]

First, I'm new to using Power BI and formulas so forgive me ... 

I have a simple Excel Workbook that I'm using for my data set.  The sheet contains every transaction that is completed in our software.  The goal is to make the formula go row by row, determine the type of transaction and if it's a certain account then do calculations depending on the criteria.  This is to calculate revenue made from each transaction.  Revenue formula:

    Cash transactions =

      25% of ActualSalePrice

      UNLESS it's from AccountID 12345 then it's 50% of (ActualSalePrice - Cost)

    Rx Plan transactions =

      25% of (TotalAmountPaid - PatientPayAmount)

      UNLESS it's from AccountID 12345 then it's 50% of (TotalAmountPaid - Cost)

 Here's the Logic written out: 

SUMX

  IF TransactionType is Rx Plan THEN

    IF AccountID is 12345 THEN TotalAmountPaid - Cost / 2

    ELSE TotalAmountPaid - PatientPayAmount * .25

  ELSE IF AccountID is 12345 THEN ActualSalesPrice - Cost / 2 ELSE

    ActualSalePrice * .25

Here's the Code I tried using (whic has everything Except the last IF which I hadn't added in yet) :

REVENUE = sumx('Sheet1',
IF('Sheet1'[TransactionType] = "Rx Plan",
IF('FullTransactionDetail (4)'[AccountID] ="12345",
('FullTransactionDetail (4)'[TotalAmountPaid] - 'FullTransactionDetail (4)'[Cost] / 2),
('FullTransactionDetail (4)'[TotalAmountPaid] - 'FullTransactionDetail (4)'[PatientPayAmount]) * .25),
'FullTransactionDetail (4)'[ActualSalePrice] * .25 ))

Power BI accepts the above formula but then when I go back to the report the charts have a big X and details says "Couldn't load the data for this visual.  Calculation error in measure 'Sheet1'[REVENUE].  DAX comparison operations do not support comparing values of type integer with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values."

If anyone can help me determine if what I want to accomplish can be done using the SUMX and nested IFs after it or if I should be using something else please make a suggestion and appreciate if you can help with that code.  I'm not a developer or coder so I have very limited coding skills 😉   Really appreciate your help! 

[AlB · Accepted solution]

@ZIggyH 

Try SWITCH for that.  Check if the code below is what you mean. If not, I would need the pseudocode as earlier. Note that for Check and  Cash 340B I've just filled in with random code as an example.

Regarding the layout, you can use daxformatter.com  It's quite convenient

REVENUE =
SUMX (
    Sheet1,
    SWITCH (
        Sheet1[TransactionType],
        "Rx Plan",
            IF (
                Sheet1[AccountID] = 12345,
                ( Sheet1[TotalAmountPaid] - Sheet1[Cost] ) / 2,
                ( Sheet1[TotalAmountPaid] - Sheet1[PatientPayAmount] ) * .25
            ),
        "Cash",
            IF (
                Sheet1[AccountID] = 12345,
                ( Sheet1[ActualSalePrice] - Sheet1[Cost] ) / 2,
                ( Sheet1[ActualSalePrice] * .25 )
            ),
        "Check",
            IF (
                Sheet1[AccountID] = 6789,
                ( Sheet1[ActualSalePrice] - Sheet1[Cost] ) / 2,
                ( Sheet1[TotalAmountPaid] - Sheet1[PatientPayAmount] ) * .25
            ),
        "Cash 340B",
            IF (
                Sheet1[AccountID] = 6789,
                ( Sheet1[ActualSalePrice] - Sheet1[Cost] ) / 2,
                ( Sheet1[ActualSalePrice] * .25 )
            )
    )
)

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[ZIggyH]

Perfect!  I knew about SWITCH, but wasn't sure how to use it with SUMX so this is awesome.  Thanks so much for your help and for the website to format DAX, I'll use it for sure.  You're the best! 

[AlB]

@ZIggyH 

This will implement the pseudocode you have in your first post. What I don't understand is that your last post's solution does check for "Cash" when your initial pseudocode does NOT.  

REVENUE =
SUMX (
    Sheet1,
    IF (
        Sheet1[TransactionType] = "Rx Plan",
        IF (
            Sheet1[AccountID] = 12345,
            ( Sheet1[TotalAmountPaid] - Sheet1[Cost] ) / 2,
            ( Sheet1[TotalAmountPaid] - Sheet1[PatientPayAmount] ) * .25
        ),
        IF (
            Sheet1[AccountID] = 12345,
            ( Sheet1[ActualSalePrice] - Sheet1[Cost] ) / 2,
            ( Sheet1[ActualSalePrice] * .25 )
        )
    )
)

This is your pseudocode I'm referring to:

SUMX

IF TransactionType is Rx Plan THEN

IF AccountID is 12345 THEN TotalAmountPaid - Cost / 2

ELSE TotalAmountPaid - PatientPayAmount * .25

ELSE IF AccountID is 12345 THEN ActualSalesPrice - Cost / 2 ELSE

ActualSalePrice * .25

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[ZIggyH]

Awesome, I beleive this works!!  Very nicely laid out.  

You are correct, the first post didn't check for cash but the second one did.  I left that part out initially.  

Question, now that we have the revenue checking if it's an Rx or Cash transaction and then checking under each if it's a certain account to do calculations, is it possible to have it check a total of 4 different transaction types with the sub checks?  There are two more tranasction types to check for, even though less used they are a possibility, Check and Cash 340B.  If it is possible, how would that look?  Thanks so much for your help, really appreciate it!!!

[AlB]

Hi @ZIggyH 

I haven't looked at the code in details but the error you get says it clearly, you are comparing numbers and text. You'd have to review the comparisons you are doing and check the values compared are of the same type. Most likely the issue is at

'FullTransactionDetail (4)'[AccountID] ="12345"

'FullTransactionDetail (4)'[AccountID] is probably of type number so the comparison should be 

'FullTransactionDetail (4)'[AccountID] = 12345

Check if you have more cases like that

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[ZIggyH]

Thanks for responding so quickly.  You were absolutely right about that part, it didn't need the double quotes around it.  Such a simple thing, but since I'm rusty on this stuff I easily missed it.  

I was able to get the function to run, but it doesn't seem like it's calculating correctly.  I wonder if the order or way I did the IFs is making it miss some?  Also, I think I have to add more IFs because if I don't then after the first couple of IF ELSE IF ELSE then it'll stop .. so here's what I did, but don't think it's calculating right (notice I changed the table name to sheet1 to shorten it):

REVENUE = sumx(Sheet1,

  IF(Sheet1[TransactionType]="Rx Plan",

    IF(Sheet1[AccountID]=12345,

      (Sheet1[TotalAmountPaid]-Sheet1[Cost])/2),

        IF(Sheet1[TransactionType]="Rx Plan",

          IF(Sheet1[AccountID]<>12345,

            (Sheet1[TotalAmountPaid]-Sheet1[PatientPayAmount])*.25),

          IF(Sheet1[TransactionType]="Cash",

       IF(Sheet1[AccountID]=12345,

     (Sheet1[ActualSalePrice]-Sheet1[Cost])/2),

(Sheet1[ActualSalePrice] * .25)))))