Table Dax Calculation

[shreya_gulati40]

I have a table based on certain criteria, i need to use DAX 

[AmiraBedh]

Try the following : 

Q1 Payout = 
VAR BaseSalary = SalesData[Base salary]
VAR Q1Units = SalesData[Q1 units]
VAR OrderType = SalesData[Order Type]
VAR Tier =
    SWITCH(TRUE(),
        Q1Units >= 20, "Tier3",
        Q1Units >= 5, "Tier2",
        "Tier1"
    )
VAR TierAdjusted =
    SWITCH(Tier,
        "Tier3", "Tier2",
        "Tier2", "Tier1",
        "Tier1", "Tier1" // No tier below Tier1
    )
VAR PayoutPercentage =
    SWITCH(TRUE(),
        AND(TierAdjusted = "Tier1", OrderType = "Initial"), 0.1,
        AND(TierAdjusted = "Tier1", OrderType = "Followon"), 0.05,
        AND(TierAdjusted = "Tier2", OrderType = "Initial"), 0.2,
        AND(TierAdjusted = "Tier2", OrderType = "Followon"), 0.1,
        AND(TierAdjusted = "Tier3", OrderType = "Initial"), 0.3,
        AND(TierAdjusted = "Tier3", OrderType = "Followon"), 0.2,
        0 // Default case if none above match
    )
RETURN
BaseSalary * PayoutPercentage

[shreya_gulati40]

O

[AmiraBedh · Accepted solution]

you'll need to map the % Q1 or % Q2 to the corresponding tier based on the percentage range provided in your payout table and  evaluate the source (M1 or M2) and the number of units to apply the correct logic for determining if it meets the condition for >=20 units or <20 units.

Q1 Payout Calculation = 
VAR BaseSalary = SalesData[Base salary]
VAR Q1Units = SalesData[Q1 units]
VAR Source = SalesData[Source]
VAR PercentQ1 = SalesData[% Q1]
VAR InitialOrFollowon = IF(Q1Units >= 20, ">=20 units", "<20 units") // Simplified assumption
VAR TierBasedOnPercent =
    SWITCH(TRUE(),
        PercentQ1 <= 50, "Tier1",
        PercentQ1 > 50 && PercentQ1 <= 90, "Tier2",
        PercentQ1 > 90, "Tier3",
        "Tier1" // Default case if no match
    )
VAR TierAdjustedForQuarter = 
    SWITCH(TierBasedOnPercent,
        "Tier3", "Tier2",
        "Tier2", "Tier1",
        "Tier1", "Tier1" // No tier below Tier1
    )
VAR PayoutPercentage =
    SWITCH(TRUE(),
        AND(Source = "M1", InitialOrFollowon = ">=20 units", TierAdjustedForQuarter = "Tier1"), 0.1,
        AND(Source = "M1", InitialOrFollowon = "<20 units", TierAdjustedForQuarter = "Tier1"), 0.05,
        AND(Source = "M2", InitialOrFollowon = ">=5 units", TierAdjustedForQuarter = "Tier1"), 0.1, // Assuming similar logic for M2
        // Add additional conditions for each combination of Source, InitialOrFollowon, and TierAdjustedForQuarter
        0 // Default case if none above match
    )
RETURN
BaseSalary * PayoutPercentage