Calculating the 'accept' and 'reject' rate

[Anonymous]

Hi Community,

I have what is turning out to be a complex problem to solve. I have a simple data set with the following columns:

• Customer - 2 different customers (Joe's Plumbing & Bob's Electrical)
• Month - 4 time periods shown Jan-April
• Status - This column will contain an 'A' for accept or 'R' for reject
• Amount - This is the $ amount that was either accepted or rejected

Customer	Month	Status	Amount
Joe's Plumbing	Jan	A	$        48
Joe's Plumbing	Jan	R	$        99
Joe's Plumbing	Jan	R	$        81
Joe's Plumbing	Jan	A	$        41
Joe's Plumbing	Feb	A	$        79
Joe's Plumbing	Feb	R	$        97
Joe's Plumbing	Feb	R	$        36
Joe's Plumbing	Feb	R	$        41
Joe's Plumbing	Mar	R	$        13
Joe's Plumbing	Mar	A	$        34
Joe's Plumbing	Mar	R	$        50
Joe's Plumbing	Mar	R	$        95
Joe's Plumbing	April	A	$        79
Joe's Plumbing	April	A	$        62
Joe's Plumbing	April	A	$        37
Joe's Plumbing	April	R	$        12
Bob's Electrical	Jan	A	$        76
Bob's Electrical	Jan	R	$        41
Bob's Electrical	Jan	A	$        90
Bob's Electrical	Jan	A	$        22
Bob's Electrical	Feb	R	$        31
Bob's Electrical	Feb	R	$        94
Bob's Electrical	Feb	A	$        74
Bob's Electrical	Feb	R	$        32
Bob's Electrical	Mar	R	$        35
Bob's Electrical	Mar	A	$        22
Bob's Electrical	Mar	A	$        27
Bob's Electrical	Mar	R	$        53
Bob's Electrical	April	A	$        30
Bob's Electrical	April	R	$        29
Bob's Electrical	April	A	$        15
Bob's Electrical	April	R	$        72

The question that I'm trying to answer and show as a visual line chart is what is the acceptance and rejection rate for each customer over the course of the 4 month period.

Using a pivot table, this is the aggregated data for each month

The final output I am looking for is a line chart that i can show the A and R rates for each customer. Using Joe's Plumbing as example below...

Any help would be appreciated. Thank you!

[Anonymous]

@gmsamborn Your solution worked!!!  Thank you sooo much!!

[gmsamborn]

You're welcome.  I'm glad it worked.

[gmsamborn · Accepted solution]

Hi @Anonymous 

Would these measure help?

Pct Accepted = 
    DIVIDE(
        CALCULATE(
            SUM( 'Table'[Amount] ),
            'Table'[Status] = "A"
        ),
        SUM( 'Table'[Amount] )
    )


Pct Rejected = 
    DIVIDE(
        CALCULATE(
            SUM( 'Table'[Amount] ),
            'Table'[Status] = "R"
        ),
        SUM( 'Table'[Amount] )
    )

Let me know if you have any questions.

Using Small Multiples

Calculating the accept and reject rate.pbix