Forum Discussion
Helper IUsing 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 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.
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!!
12 Replies
- PijushRoy
Community Champion
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_hfhsBoth '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- 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
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!!
ExcelMonkeImpactful Individual
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
- 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
What is the format of your surgeries column? Is it text? Numbers?
- Thamizh_hfhsSurgeries# = IF(ISBLANK(DISTINCTCOUNT('Surgeries'[LogID])), 0, DISTINCTCOUNT('Surgeries'[LogID]))
LogID is text.
- 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)
