Calculate Sum and Filter with IF conditions

[Pdawar]

Hi,

I have a monthly trend table where I need to calculate the sum of patients with few filters and conditions applied. See the below measure - 

Patients_Adj =

var test = CALCULATE(SUM(Patients[Patients]),Patients[TYPE] = "Paid", Patients[PRODUCT_FK] = "Product A")

return

IF(MAX('Date'[Month])>=[max_Date],test,0)

This measure is giving correct output at row level, but totals are incorrect because this is doing the aggregation first and then applying the return condition. Is there a way I can re-write this measure so that conditions are applied first and then the sum aggregation happens. This is important because I need to calculate the cumulative number on top of this measure so that totals has to be accurate.

Below is the table that I am using - 

 Thanks!

[v-shex-msft]

Hi @Pdawar,

It sounds like a common multiple level aggregation calculate requirement in Dax. You can refer to the Greg’s blog to know how to handle this scenario: (SUMMARIZE function and iterator aggregation functions)

Measure Totals, The Final Word 

Regards,

Xiaoxin Sheng

[Pdawar]

I am not sure how to apply this because it involves applying the if condition as well. Also, the filter that we need to apply are from different tables as the data model is star schema. If we go ahead with this measure, how can we apply filter conditions from different tables and if conditions?

[123abc]

Certainly! It looks like you want to calculate the sum of patients with specific filters and conditions, and you want these conditions to be applied before aggregation to get accurate totals. To achieve this, you can use the FILTER function along with SUMX instead of CALCULATE. Here's a modified version of your measure:

Patients_Adj =
SUMX (
FILTER (
Patients,
Patients[TYPE] = "Paid" &&
Patients[PRODUCT_FK] = "Product A" &&
'Date'[Month] >= [max_Date]
),
Patients[Patients]
)

This measure uses the FILTER function to create a table where the specified conditions are met, and then uses SUMX to iterate over that table and calculate the sum of patients. This way, the conditions are applied before the aggregation.

Make sure to replace [max_Date] with the appropriate reference to the maximum date you're comparing against. This measure should provide accurate totals because it applies the conditions at the row level before summing up the values.