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
Denskim123
Regular Visitor

Calculating the 'accept' and 'reject' rate

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
CustomerMonthStatusAmount
Joe's PlumbingJan A  $        48
Joe's PlumbingJan R  $        99
Joe's PlumbingJan R  $        81
Joe's PlumbingJan A  $        41
Joe's PlumbingFeb A  $        79
Joe's PlumbingFeb R  $        97
Joe's PlumbingFeb R  $        36
Joe's PlumbingFeb R  $        41
Joe's PlumbingMar R  $        13
Joe's PlumbingMar A  $        34
Joe's PlumbingMar R  $        50
Joe's PlumbingMar R  $        95
Joe's PlumbingApril A  $        79
Joe's PlumbingApril A  $        62
Joe's PlumbingApril A  $        37
Joe's PlumbingApril R  $        12
Bob's ElectricalJan A  $        76
Bob's ElectricalJan R  $        41
Bob's ElectricalJan A  $        90
Bob's ElectricalJan A  $        22
Bob's ElectricalFeb R  $        31
Bob's ElectricalFeb R  $        94
Bob's ElectricalFeb A  $        74
Bob's ElectricalFeb R  $        32
Bob's ElectricalMar R  $        35
Bob's ElectricalMar A  $        22
Bob's ElectricalMar A  $        27
Bob's ElectricalMar R  $        53
Bob's ElectricalApril A  $        30
Bob's ElectricalApril R  $        29
Bob's ElectricalApril A  $        15
Bob's ElectricalApril 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

Denskim123_0-1710536424901.png

 

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...

 

Denskim123_0-1710537003590.png

 

 

Any help would be appreciated. Thank you!

 

1 ACCEPTED SOLUTION
gmsamborn
Super User
Super User

Hi @Denskim123 

 

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

 

View solution in original post

3 REPLIES 3
Denskim123
Regular Visitor

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

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

gmsamborn
Super User
Super User

Hi @Denskim123 

 

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

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.