Forum Discussion

donovanm's avatar
donovanm
Frequent Visitor
1 year ago
Solved

IF AND Multiple Conditions

Hi,   I need to create a column where I end up with four possible outcomes based on two conditions relating to various contracts status and the corresponding premium figure.   In Excel it would b...
Need Help
  • donovanm's avatar
    donovanm
    1 year ago

    Hi FarhanJeelani 

     

    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.

     

     

v-hashadapu's avatar
v-hashadapu
Community Support
1 year ago

Hi donovanm , Thank you for reaching out to the Microsoft Community Forum.

 

The issue is that your DAX formula is being applied at the row level (monthly premium), whereas you need the category to be determined based on the total premium per contract. To fix this, you should create a measure instead of a calculated column, as measures can dynamically calculate the total premium across months.

Please try this:

  1. Create a measure:

Category Measure =

VAR TotalPremium =

    CALCULATE(

        SUM('Analytics vwSnapshotEstimatedPremiumsv2'[SlipPremiumChaucerShare]),

        ALLEXCEPT('Analytics vwSnapshotEstimatedPremiumsv2', 'Analytics vwSnapshotEstimatedPremiumsv2'[ContractID])

    )

 

RETURN

    SWITCH(

        TRUE(),

        SELECTEDVALUE('Analytics vwSnapshotEstimatedPremiumsv2'[Lead/Follow]) = "Lead" && TotalPremium >= 250000, 1,

        SELECTEDVALUE('Analytics vwSnapshotEstimatedPremiumsv2'[Lead/Follow]) = "Lead" && TotalPremium < 250000, 2,

        SELECTEDVALUE('Analytics vwSnapshotEstimatedPremiumsv2'[Lead/Follow]) = "Follow" && TotalPremium >= 250000, 3,

        4

    )

  1. Use this measure in your table or matrix visual. Ensure that ContractID (or an equivalent unique contract identifier) is part of your visual to group data correctly.
  2. Validate the results by adding columns for ContractID, Lead/Follow, Total Premium, and the Category Measure to your visual.
  3. Verify that the categorization is correct based on the total premium: If Total Premium >= 250K and Lead, it should show Category 1, If Total Premium < 250K and Lead, it should show Category 2, If Total Premium >= 250K and Follow, it should show Category 3, If Total Premium < 250K and Follow, it should show Category 4.

Thank you FarhanJeelani for your prompt and appropriate answer.

 

If this helped solve the issue, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.