Need a help of dax for calculating complicated conditions

[sakuragihana]

Hello everyone, I have 2 table below :

Table 1:

Table 2: 

Table 2 is explained below :

Condition :

1.% New Std PD and Re open vs Target >= 90%

2.If % New Student PD vs New Enrol PD Target < 80% , the bonus will be lowered by 1 level ( Note : If the New Student PD vs New Enrol PD Target is smaller than 80% and the bonus at level 1, the bonus will not be received)

3.If % New Student PD vs New Enrol PD Target < 80% and Campus code is Group 6 or Group 7, the bonus will be lowered by 1 level (Note : If the New Student PD vs New Enrol PD Target is smaller than 80% and the bonus at level 1, the bonus will not be minus 500,000)

Example 1:

Campus NCT has :

% New Std PD and Re open vs Target = 103%

% New Student PD vs New Enrol PD Target = 75%

Bonus of Campus NCT (belong Group 2 ) is level 2: 9,000,000 ( with % New Std PD and Re open vs Target =103%) . However, because % New Student PD vsa New Enrol PD Target is smaller than 80% , so the bonus will be lowered by 1 level . The bonus will be level 1 =7,000,000

Example 2:

Campus BD-BCMb has:

% New Std PD and Re open vs Target = 98%

% New Student PD vs New Enrol PD Target = 50%

Bonus of Campus BD-BCM (belong Group 6) is level 1 : 3,000,000 ( with % New Std PD and Re open vs Target =98%) . However, because % New Student PD vsa New Enrol PD Target is smaller than 80% , so the bonus will be : 2,500,000

I make a power BI file with table data and bonus table in this link : https://drive.google.com/file/d/1o5-XnoSiHyRBrzBz-_raxWZxKkqtAMYN/view?usp=share_link 

Can everyone help me to apply all of conditions for dax to calculate the bonus of campus ?

[MAwwad]

Try:

Bonus =
VAR StdPDReopen =
SUM ( 'Table 1'[New Std PD & Reopen] )
VAR TargetStdPDReopen =
SUM ( 'Table 2'[New Std PD & Reopen Target] )
VAR NewStdPD =
SUM ( 'Table 1'[New Student PD] )
VAR EnrolPD =
SUM ( 'Table 1'[New Enrol PD] )
VAR TargetNewStdPD =
SUM ( 'Table 2'[New Student PD vs New Enrol PD Target] )
VAR CampusCode =
SELECTEDVALUE ( 'Table 1'[Campus Code] )
VAR BonusTable =
SELECTCOLUMNS (
'Table Bonus',
"Group", 'Table Bonus'[Group],
"Level", 'Table Bonus'[Level],
"Bonus", 'Table Bonus'[Bonus]
)
VAR BonusLevel =
SWITCH (
TRUE (),
StdPDReopen / TargetStdPDReopen >= 0.9, 1,
StdPDReopen / TargetStdPDReopen >= 0.8, 2,
StdPDReopen / TargetStdPDReopen >= 0.7, 3,
StdPDReopen / TargetStdPDReopen >= 0.6, 4,
StdPDReopen / TargetStdPDReopen >= 0.5, 5,
StdPDReopen / TargetStdPDReopen >= 0.4, 6,
StdPDReopen / TargetStdPDReopen >= 0.3, 7,
StdPDReopen / TargetStdPDReopen >= 0.2, 8,
StdPDReopen / TargetStdPDReopen >= 0.1, 9,
10
)
VAR Bonus =
LOOKUPVALUE ( BonusTable[Bonus], BonusTable[Group], CampusCode, BonusTable[Level], BonusLevel )
RETURN
IF (
TargetNewStdPD >= 0.8,
Bonus,
IF (
BonusLevel = 1,
0,
IF (
CampusCode = "Group 6" || CampusCode = "Group 7",
MAX ( 0, Bonus - 500000 ),
MAX ( 0, Bonus - 1000000 )
)
)
)

1. First, we define a few variables to make the formula easier to read and maintain. These variables calculate the relevant values from the two tables you provided.
2. Next, we create a table that maps campus codes and bonus levels to bonus amounts. This table will be used later to look up the bonus amount based on the campus code and bonus level.
3. We calculate the bonus level based on the % New Std PD and Reopen vs Target. We use a SWITCH statement to determine the bonus level based on a series of conditions.
4. We look up the bonus amount from the BonusTable using the campus code and bonus level.
5. Finally, we calculate the bonus amount based on the various conditions you provided. If the % New Student PD vs New Enrol PD Target is greater than or equal to 80%, we simply return the bonus amount as calculated in step 4. If it's less than 80%, we check if the bonus level is already at the lowest level (1). If it is, we return 0. If it's not, we check if the campus code is Group 6 or Group 7. If it is, we subtract 500,000 from the bonus amount. Otherwise,

[sakuragihana]

Hi @MAwwad,

The dax is not working , Can you sent me a file pilb ? I attached a file with data in that link above. Thank you

Note : all of type condition are calculating for March because in April bonus will be change the value. Can the measure apply the conditions for month ?