Forum Discussion

Thamizh_hfhs's avatar
Thamizh_hfhs
Icon for Helper I rankHelper I
2 years ago
Solved

Using SUMMARIZE and SUMX with IF statement

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 Vo...
Need Help
  • Thamizh_hfhs's avatar
    Thamizh_hfhs
    2 years ago

    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!!

ExcelMonke's avatar
ExcelMonke
Icon for Impactful Individual rankImpactful Individual
2 years ago

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's avatar
Thamizh_hfhs
Icon for Helper I rankHelper I
2 years ago

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)