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.
All-
new to this forum, so thanks for this. I work in fraud prevention and I am trying to write a measure that will help me compute the total approval percentage of my sales minus a subset of orders that were declined by business rules.
I have written up what I want the measure to say and then wrote it out with DAX the best I could showing the table names and even the column names and what text I am looking for in those columns.
Please help me with what I am doing wrong.
Measure- Business rule declines = calculate the total amount of orders that were declined by business rules and subtract that from total amount of orders
calculate(SUM(POWER bi cybs DATA 3 [ORDER AMOUNT] - (POWER BI CYBS DATA 3 [ORDER AMOUNT]) WHEN FILTERED(SHEET 2 [BUSINESS OR FRAUD], "BUSINESS")
measure - Business rule total amount = then take that first measure and subtract it from the total amount of the whole amount
CALCULATE(SUM(POWER BI CYBS DATA 3(ORDER AMOUNT) - MEASURE(BUSINESS RULE DECLINES)
measure- Business fraud approval rate = calculate the total amount for orders system rejected plus reviewer reject and divide it by measure business rule total amount
CALCULATE(SUM(POWER BI CYBS DATA 3[ORDER AMOUNT] FILTERED(POWER BI CYBS DATA 3[ACTIVE PROFILE DECISION], "REJECT") + (SUM(POWER BI CYCB DATA 3[ORDER AMOUNT]) FILTERED(CASE MANAGER DECISION), "REVIEWER REJECT") / MEASURE BUSINESS RULE TOTAL AMOUNT
Solved! Go to Solution.
Hi @MJAGUSIAK ,
Try measures as below.
Measure- Business rule declines:
Business rule declines =
VAR _Total =
CALCULATE ( SUM ( 'POWER bi cybs DATA 3'[ORDER AMOUNT] ) )
VAR _DeclinedTotal =
CALCULATE (
SUM ( 'POWER bi cybs DATA 3'[ORDER AMOUNT] ),
FILTER ( 'SHEET 2', 'SHEET 2'[BUSINESS OR FRAUD] = "BUSINESS" )
)
RETURN
_Total - _DeclinedTotal
Measure - Business rule total amount:
Business rule total amount =
CALCULATE ( SUM ( 'POWER BI CYBS DATA 3'[ORDER AMOUNT] ) ) - [Business rule declines]
Measure- Business fraud approval rate:
Business fraud approval rate =
VAR _system_reject =
CALCULATE (
SUM ( 'POWER BI CYBS DATA 3'[ORDER AMOUNT] ),
FILTER (
'POWER BI CYBS DATA 3',
'POWER BI CYBS DATA 3'[ACTIVE PROFILE DECISION] = "REJECT"
)
)
VAR _reviewer_reject =
CALCULATE (
SUM ( 'POWER BI CYCB DATA 3'[ORDER AMOUNT] ),
FILTER ( 'Table', 'Table'[CASE MANAGER DECISION] = "REVIEWER REJECT" )
)
RETURN
DIVID(_system_reject+_reviewer_reject,[Business rule total amount])
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @MJAGUSIAK ,
Try measures as below.
Measure- Business rule declines:
Business rule declines =
VAR _Total =
CALCULATE ( SUM ( 'POWER bi cybs DATA 3'[ORDER AMOUNT] ) )
VAR _DeclinedTotal =
CALCULATE (
SUM ( 'POWER bi cybs DATA 3'[ORDER AMOUNT] ),
FILTER ( 'SHEET 2', 'SHEET 2'[BUSINESS OR FRAUD] = "BUSINESS" )
)
RETURN
_Total - _DeclinedTotal
Measure - Business rule total amount:
Business rule total amount =
CALCULATE ( SUM ( 'POWER BI CYBS DATA 3'[ORDER AMOUNT] ) ) - [Business rule declines]
Measure- Business fraud approval rate:
Business fraud approval rate =
VAR _system_reject =
CALCULATE (
SUM ( 'POWER BI CYBS DATA 3'[ORDER AMOUNT] ),
FILTER (
'POWER BI CYBS DATA 3',
'POWER BI CYBS DATA 3'[ACTIVE PROFILE DECISION] = "REJECT"
)
)
VAR _reviewer_reject =
CALCULATE (
SUM ( 'POWER BI CYCB DATA 3'[ORDER AMOUNT] ),
FILTER ( 'Table', 'Table'[CASE MANAGER DECISION] = "REVIEWER REJECT" )
)
RETURN
DIVID(_system_reject+_reviewer_reject,[Business rule total amount])
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If you can provide image of sample data, it would make this easier.
Currently, the formulas you have are not syntatically correct. Especially the sums, not having closing parenthesis at the right places.
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 |
---|---|
49 | |
25 | |
20 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |