Using SUMMARIZE and SUMX with IF statement

[Thamizh_hfhs]

I have surgeries #, last 12 months average of surgeries, Variance #, Current month Avg cost per case, Average of last 12 months cost per case. I'm trying to calculate the $Amount Impact of Surgery Volumes and summarize it by Operating Unit then Service Line and followed my Procedures. Now, there are two scenarios here where I use IF statement.

1. IF Surgeries # <> 0 and last 12 months average of surgeries <> 0, then Variance# * Current month Avg cost per case
2. IF Surgeries # = 0 and last 12 months average of surgeries <> 0, then Variance# * Average of last 12 months cost per case.

When I define this in the DAX, only the 1st criteria is working. The 2nd criteria doesn't seem to be working. Attaching the DAX below,

$ Amt Impact due to Volumes = 
var AvgCPC12months = SUMX(
    SUMMARIZE(Surgeries,'Surgeries'[Operating Unit], 'Surgeries'[PrimaryService], 'Surgeries'[PrimaryProcedureDescription]), 'Surgeries'[Variances#] * 'CPC'[Last 12 months Avg CPC]) 

var AvgCPCCurrentMonth = SUMX(
    SUMMARIZE(Surgeries,'Surgeries'[Operating Unit], 'Surgeries'[PrimaryService], 'Surgeries'[PrimaryProcedureDescription]), 'Surgeries'[Variances#] * 'CPC'[Avg Cost per Case]) 

RETURN 
    IF('Surgeries'[Surgeries#] = 0 && 'Surgeries'[Last 12 Months Avg#] <> 0, AvgCPC12months, AvgCPCCurrentMonth)

 Any help will be much appreciated.

[ExcelMonke]

Try:

Surgeries# = CALCULATE(DISTINCTCOUNT('Surgeries'[LogID]),'Surgeries'[LogID]<> "")

if that doesn't replicate the result, an alternative is:

Surgeries# = CALCULATE(DISTINCTCOUNT('Surgeries'[LogID]),FILTER('Surgeries',NOT ISBLANK('Surgeries'[LogID]))

This should be able to solve your formatting issue (and will also speed up calculations compared to the IF statement)

[Thamizh_hfhs]

The measures you suggested basically will exclude all the NULL Surgeries. I actually need them in the data for calcualting the negative dollar amount impact (Condition 2 as mentioned in the description above)

[ExcelMonke]

 What is the format of your surgeries column? Is it text? Numbers?

[Thamizh_hfhs]

Surgeries# = IF(ISBLANK(DISTINCTCOUNT('Surgeries'[LogID])), 0, DISTINCTCOUNT('Surgeries'[LogID]))
LogID is text.

[ExcelMonke]

Consider adding a calculate + filter function. So for example,

CALCULATE([your measure], FILTER(Table, Table[Surgeries] <>""))

[Thamizh_hfhs]

I tried CALCULATE('Surgeries'[$ Amt Impact due to Volumes], FILTER('Surgeries', 'Surgeries'[Surgeries#] <> ""))

And I got the followiing error message,

[ExcelMonke]

Consider putting your 

'Surgeries'[Variances#] * 'CPC'[Avg Cost per Case]

 clause as the first variable.

Could you perhaps show us what the result is giving you now, and what the intended result should be?

[Thamizh_hfhs]

I did try adding that as first variable. This is the result I get. 

Results I get

And below is the result I need,

Result I need

[PijushRoy]

Hi @Thamizh_hfhs 

DAX looks good, please check 'Surgeries'[Surgeries#] and 'Surgeries'[Last 12 Months Avg#] both are numeric datatype fields.
Try below dax

$ Amt Impact due to Volumes =
VAR AvgCPC12months =
    SUMX (
        SUMMARIZE (
            Surgeries,
            'Surgeries'[Operating Unit],
            'Surgeries'[PrimaryService],
            'Surgeries'[PrimaryProcedureDescription]
        ),
        'Surgeries'[Variances#] * 'CPC'[Last 12 months Avg CPC]
    )
VAR AvgCPCCurrentMonth =
    SUMX (
        SUMMARIZE (
            Surgeries,
            'Surgeries'[Operating Unit],
            'Surgeries'[PrimaryService],
            'Surgeries'[PrimaryProcedureDescription]
        ),
        'Surgeries'[Variances#] * 'CPC'[Avg Cost per Case]
    )
RETURN
    SWITCH(
 TRUE(),
        'Surgeries'[Surgeries#] = 0 && 'Surgeries'[Last 12 Months Avg#] <> 0,
        AvgCPC12months,
        AvgCPCCurrentMonth
    )

Let me know if that works for you

If your requirement is solved, please mark THIS ANSWER as SOLUTION ✔️ and help other users find the solution quickly. Please hit the Thumbs Up 👍 button if this comment helps you.

Thanks
Pijush
Linkedin

[Thamizh_hfhs · Accepted solution]

I found a solution. I created a simple measure with the IF statement. 

$ Amt Impact Criteria = IF('Surgeries'[Surgeries#] = 0 && 'Surgeries'[Last 12 Months Avg#] <> 0, 'Surgeries'[Variance#] * 'CPC'[Last 12 months Avg CPC], 'Surgeries'[Variance#] * 'CPC'[Avg Cost per Case])

And then I modified the existing DAX as follows

$ Amt Impact due to Volumes = SUMX(SUMMARIZE(CALCULATETABLE(CALCULATETABLE('Surgeries', ALL(DimDate)), DATESINPERIOD('DimDate'[Date], MAX('DimDate'[Date]), -12, MONTH)), 'Surgeries'[DepartmentCenter], 'Surgeries'[PrimaryService], 'Surgeries'[PrimaryProcedureDescription]),'Surgeries'[$ Amt Impact Criteria])

Thanks everyone for your help!!

[Thamizh_hfhs]

I have BLANKS( ) or NULL in the Surgeries# impacting the DAX. I tried replacing the Surgeries# = 0 with BLANK or " " or "0" in the DAX. None of them worked. 

[Thamizh_hfhs]

Both 'Surgeries'[Surgeries#] and 'Surgeries'[Last 12 Months Avg#]  are decimal numbers and they both are measures. 

The DAX you suggested didn't work. Same problem again. I don't see any results for the procedures with the following condition 

'Surgeries'[Surgeries#] = 0 && 'Surgeries'[Last 12 Months Avg#] <> 0