Forum Discussion

jakenguyen02's avatar
jakenguyen02
Frequent Visitor
2 years ago
Solved

Division with multiple Filters

I've been trying to figure this out for the last few hours and don't know if it's possible. I have 3 audits and they look at different things. I want to divide the issue by the audit count of what is reviewed to get the actual error percentage. 

 

Please see my example below. 

 

This shows what's being audited per audit type.

 

 

This is my expected output. 

 

Thank you.

 

 

  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi @ jakenguyen02,

     

    Based on the data you provided, I created the following two tables.

    Table1

     

     

    Table2

     

    In Table 1, add a calculated column (AT_count) to calculate the audit count for the row with an AuditType of Yes.

    AT_count =

  VAR _AT1 = IF(Table1[Audit Type1] = "Yes" , MIN(Table2[Total Audit Completed]) , 0)

  VAR _AT2 = IF(Table1[Audit Type2] = "Yes" , MIN(Table2[Total Audit Completed]) , 0)

  VAR _AT3 = IF(Table1[Audit Type3] = "Yes" , MIN(Table2[Total Audit Completed]) , 0)

  RETURN

  _AT1+_AT2+_AT3

     

    In Table 1, then add a calculated column Error Rate to calculate the actual error percentage.

    Error Rate = Table1[Issue Count]/Table1[AT_count]

    You can then construct a visual object table that meets your goals.

     

    Best Regards,

    Dengliang Li

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi @ jakenguyen02,

     

    Based on the data you provided, I created the following two tables.

    Table1

     

     

    Table2

     

    In Table 1, add a calculated column (AT_count) to calculate the audit count for the row with an AuditType of Yes.

    AT_count =

  VAR _AT1 = IF(Table1[Audit Type1] = "Yes" , MIN(Table2[Total Audit Completed]) , 0)

  VAR _AT2 = IF(Table1[Audit Type2] = "Yes" , MIN(Table2[Total Audit Completed]) , 0)

  VAR _AT3 = IF(Table1[Audit Type3] = "Yes" , MIN(Table2[Total Audit Completed]) , 0)

  RETURN

  _AT1+_AT2+_AT3

     

    In Table 1, then add a calculated column Error Rate to calculate the actual error percentage.

    Error Rate = Table1[Issue Count]/Table1[AT_count]

    You can then construct a visual object table that meets your goals.

     

    Best Regards,

    Dengliang Li

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.