Forum Discussion

MJAGUSIAK's avatar
MJAGUSIAK
Icon for Helper I rankHelper I
4 years ago
Solved

Need help with measure when summing filtered data

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

 

  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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.

  • ahmedoye's avatar
    ahmedoye
    Icon for Responsive Resident rankResponsive Resident
    4 years ago

    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.