Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
sakuragihana
Helper IV
Helper IV

Need a help of dax for calculating complicated conditions

Hello everyone, I have 2 table below :

Table 1:

sakuragihana_0-1678093527448.png

Table 2: 

sakuragihana_0-1678161751540.png

 

Table 2 is explained below :

Screenshot 2023-03-06 160944.png

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 ?

 

2 REPLIES 2
MAwwad
Super User
Super User

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,

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 ?

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.