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

Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper 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)