Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello everyone, I have 2 table below :
Table 1:
Table 2:
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 ?
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 )
)
)
)
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 ?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
43 | |
20 | |
18 | |
16 | |
15 |
User | Count |
---|---|
49 | |
26 | |
22 | |
17 | |
16 |