Calculating Fail Rate

[Anonymous]

Hi,

I'm trying to do a simple calculations in Power BI with DAX but I can't think of a way to do it for the life of me. I was hoping someone could help.

So basically, I have 2 sheets in my Excel file. One of Failed and one is Total.

On the Failed sheet, all the failed trades for the month is listed out with broker names next to each trade.

On the Total sheet, all the trades for the month booked is listed out, also with broker names next to each trade.

I'm trying to calculcate percentage of failed trades, e.g., if there are 5 trades with broker A in Failed sheet, and 10 trades with broker A in the Total sheet, the Fail rate would be 50%.

I tried using Divide and Count in Power BI but it ended up dividing the 5 failed trades by all the trades with all the brokers in the Total sheets, rather than just dividing by the trades specifically with Broker A in the Total Sheet.

Thank you in advance!

[amitchandak]

@Anonymous , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

You need to create a common broker table and Date table(need to check data for that)

[Anonymous]

Hi amitchandak

Thanks for your help.

I have the sample data below.

So from this example, the fail rates are as below, which is also what I'm looking to show in the Power BI report. Just the table.

Broker A - 50%

Broker C - 10%

Calculation: Broker A has 6 trades in Total Volume and 3 of them failed and showed in Failed Tab. (3/6 = 0.5)

SImilarly, Broker C has 10 trades in Total Volume and 1 of them failed and showed in failed tab. (1/10 = 0.1)

Can i use the broker name as the common reference to calculate this?

Failed Tab:

Trade Reference	Trade Date	Settlement Date	Quantity	Security	Broker
12345	22/6/20	24/6/20	120	AB Ltd	Broker A
67890	22/6/20	24/6/20	130	BC Ltd	Broker A
23456	22/6/20	24/6/20	140	De Ltd	Broker A
34567	22/6/20	24/6/20	150	BY Ltd	Broker B
45678	22/6/20	24/6/20	160	VH Ltd	Broker C
56789	22/6/20	24/6/20	170	GO Ltd	Broker C
19283	22/6/20	24/6/20	180	BU Ltd	Broker C

Total Volume tab:

Trade Reference	Trade Date	Settlement Date	Quantity	Security	Broker
12345	22/6/20	24/6/20	120	AB Ltd	Broker A
67890	22/6/20	24/6/20	130	BC Ltd	Broker A
23456	22/6/20	24/6/20	140	De Ltd	Broker A
34567	22/6/20	24/6/20	150	BY Ltd	Broker B
45678	22/6/20	24/6/20	160	VH Ltd	Broker C
56789	22/6/20	24/6/20	170	GO Ltd	Broker C
19283	22/6/20	24/6/20	180	BU Ltd	Broker C
40120.28571	22/6/20	24/6/20	220	DU LTD	Broker A
40864.35714	22/6/20	24/6/20	229	AM LTD	Broker A
41608.42857	22/6/20	24/6/20	228	AI LTD	Broker A
42352.5	22/6/20	24/6/20	227	AB LTD	Broker B
43096.57143	22/6/20	24/6/20	226	BS LTD	Broker B
43840.64286	22/6/20	24/6/20	225	OQ LTD	Broker B
44584.71429	22/6/20	24/6/20	226	DH LTD	Broker B
45328.78571	22/6/20	24/6/20	227	AO LTD	Broker B
46072.85714	22/6/20	24/6/20	227	AH LTD	Broker B
46816.92857	22/6/20	24/6/20	225	PS LTD	Broker B
47561	22/6/20	24/6/20	224	MM LTD	Broker B
48305.07143	22/6/20	24/6/20	222	AA LTD	Broker B

Thanks!

[harshnathani]

HI @Anonymous ,

Create a common Broker Table.

Join the relationship with both Failed Table and Volume Table.

Create these measures

Fail Rates =
VAR a =
    CALCULATE (
        COUNT ( 'Failed Table'[Trade Reference] ),
        FILTER (
            Broker,
            Broker[Broker]
                = MAX ( 'Failed Table'[Broker] )
        )
    )
VAR b =
    CALCULATE (
        COUNT ( 'Volume Table'[Trade Date] ),
        FILTER (
            Broker,
            Broker[Broker]
                = MAX ( 'Volume Table'[Broker] )
        )
    )
RETURN
    DIVIDE (
        a,
        b
    )

Broker TableRelationshipFinal Value

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)