Forum Discussion

Thamizh_hfhs's avatar
Thamizh_hfhs
Helper 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!!

PijushRoy's avatar
PijushRoy
Community Champion
2 years ago

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

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.