Forum Discussion
IF AND Multiple Conditions
Thank you for providing that solution. I believe that the aggregation is causing the problem here as the data has the premium monthly so the category DAX is being applied at that level. Ideally I would like to be able to total the months up and then apply the Category to the total.
e.g. The contracy below has a Total Premium of 968k but it is classfied as Lead and over the 250k threshold yet it is has a Category 2 - I assume because the monthly value is 81k.
Dear donovanm ,
Your DAX formula has a couple of issues:
SUM inside an IF condition – Since you're using SUM(), it aggregates values, which is not ideal for a calculated column. A calculated column works row-by-row and should reference individual row values instead.
Incorrect boundary for < 250000 – You have SUM(... < 249999), but it should be < 250000.
Data type mismatch – The category outputs should ideally be numeric, not strings ("1", "2", etc.).
Corrected DAX for a Calculated Column
Use this formula in Power BI calculated column (not a measure):
Category =
SWITCH(
TRUE(),
'Analytics vwSnapshotEstimatedPremiumsv2'[Lead/Follow] = "Lead" &&
'Analytics vwSnapshotEstimatedPremiumsv2'[SlipPremiumChaucerShare] >= 250000, 1,
'Analytics vwSnapshotEstimatedPremiumsv2'[Lead/Follow] = "Lead" &&
'Analytics vwSnapshotEstimatedPremiumsv2'[SlipPremiumChaucerShare] < 250000, 2,
'Analytics vwSnapshotEstimatedPremiumsv2'[Lead/Follow] = "Follow" &&
'Analytics vwSnapshotEstimatedPremiumsv2'[SlipPremiumChaucerShare] >= 250000, 3,
4
)
Please mark this post as solution if it helps you. Appreciate Kudos.