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 adding a calculate + filter function. So for example,

CALCULATE([your measure], FILTER(Table, Table[Surgeries] <>""))
  • Thamizh_hfhs's avatar
    Thamizh_hfhs
    Icon for Helper I rankHelper I
    2 years ago

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

    And I got the followiing error message,