Division with multiple Filters

[jakenguyen02]

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.

[jakenguyen02]

Thank you for your help!

[Anonymous · Accepted solution]

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.