Forum Discussion

Anonymous

Not applicable

6 years ago

Calculating Fail Rate

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

Anonymous

Not applicable

6 years ago

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!

Anonymous

Not applicable

6 years ago

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)

Related content