Forum Discussion

Anonymous's avatar
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...
Need Help
amitchandak's avatar
amitchandak
Super User
6 years ago

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's avatar
Anonymous
Not applicable
6 years ago

 

 

 

 

 

 

 

Failed Tab:

 

Trade ReferenceTrade DateSettlement DateQuantitySecurityBroker
1234522/6/2024/6/20120AB LtdBroker A
6789022/6/2024/6/20130BC LtdBroker A
2345622/6/2024/6/20140De LtdBroker A
3456722/6/2024/6/20150BY LtdBroker B
4567822/6/2024/6/20160VH LtdBroker C
5678922/6/2024/6/20170GO LtdBroker C
1928322/6/2024/6/20180BU LtdBroker C

 

Total Volume tab:

 

Trade ReferenceTrade DateSettlement DateQuantitySecurityBroker
1234522/6/2024/6/20120AB LtdBroker A
6789022/6/2024/6/20130BC LtdBroker A
2345622/6/2024/6/20140De LtdBroker A
3456722/6/2024/6/20150BY LtdBroker B
4567822/6/2024/6/20160VH LtdBroker C
5678922/6/2024/6/20170GO LtdBroker C
1928322/6/2024/6/20180BU LtdBroker C
40120.2857122/6/2024/6/20220DU LTDBroker A
40864.3571422/6/2024/6/20229AM LTDBroker A
41608.4285722/6/2024/6/20228AI LTDBroker A
42352.522/6/2024/6/20227AB LTDBroker B
43096.5714322/6/2024/6/20226BS LTDBroker B
43840.6428622/6/2024/6/20225OQ LTDBroker B
44584.7142922/6/2024/6/20226DH LTDBroker B
45328.7857122/6/2024/6/20227AO LTDBroker B
46072.8571422/6/2024/6/20227AH LTDBroker B
46816.9285722/6/2024/6/20225PS LTDBroker B
4756122/6/2024/6/20224MM LTDBroker B
48305.0714322/6/2024/6/20222AA LTDBroker B

 

 

Thanks!

  • Anonymous's avatar
    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)