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

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

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