topic Count Pass/Fail per month by KPI Priority Group (i.e. not individual jobs) in DAX Commands and Tips

Count Pass/Fail per month by KPI Priority Group (i.e. not individual jobs)

Anonymous — Sun, 18 Sep 2022 04:14:31 GMT

Hello, does anyone know how I can calculate the above?

I have a measure that evaluates an expression (checks if job date was completed before due date) and outputs "Pass" or "Fail"

I also have Priority Groups each with different target rates, and these are basicaly the KPI's (i.e. 'P1 & P2' with 100% target, 'P3 & P4' with 95% target 'S' with 98% pass target, etc)

I want to know, by Month, and for each Priority Group, the count of "Pass" and "Fail"

The main issue is getting the count of Pass/Fail based on the Priority Group and not for every single job.

Here is my measure that counts the Pass/Fail for each Priority Group:

I basically just want to count the highlighted below:

Re: Count Pass/Fail per month by KPI Priority Group (i.e. not individual jobs)

tackytechtom — Sat, 17 Sep 2022 08:42:10 GMT

Hi Anonymous ,

Would this one here help you?

Let me know! 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

Re: Count Pass/Fail per month by KPI Priority Group (i.e. not individual jobs)

Anonymous — Sat, 17 Sep 2022 09:47:06 GMT

Hi tackytechtom thank you for responding!

I'm not sure which one to apply to my situation.

e.g If I have 100 jobs in a month, each of these jobs has a priority code, and the priority code has a KPI (e.g. 95% pass rate for P3, 100% pass rate for P2, etc)

I have calculated the above using a Measure (not a calculated column)

I have tried to use SUMMARIZE but it doesn't allow me to return a text value from my measure...

Re: Count Pass/Fail per month by KPI Priority Group (i.e. not individual jobs)

tackytechtom — Sat, 17 Sep 2022 09:18:34 GMT

Hi Anonymous ,

Can you share the pbix file or some data and its outcome? 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

Re: Count Pass/Fail per month by KPI Priority Group (i.e. not individual jobs)

tamerj1 — Sat, 17 Sep 2022 09:40:01 GMT

Hi Anonymous

please try

Pass Count =
SUMX ( VALUES ( 'Table'[Job ID] ), IF ( [Measure] = "Pass", 1 ) )

Re: Count Pass/Fail per month by KPI Priority Group (i.e. not individual jobs)

Anonymous — Sat, 17 Sep 2022 09:55:50 GMT

Hopefully this may help.

Would like to get the result to give the percentage of the "pass" per month for each column category:

So for instance P1 & P2column,

Fail = 5, Pass = 1,

then divide Pass / Total to get the average pass for P2 & P2 for this year

Re: Count Pass/Fail per month by KPI Priority Group (i.e. not individual jobs)

Anonymous — Sat, 17 Sep 2022 10:04:50 GMT

Hi tamerj1

This is returning a blank...

Also I am not wanting to count the number of jobs, this is too low of a granuliarity. The jobs are roled up into KPI groups (e.g P1 & P2, P3 & P4, etc)

I have the job data in a table, the calendar data in another table, and a dimention table for my priority codes

Re: Count Pass/Fail per month by KPI Priority Group (i.e. not individual jobs)

Anonymous — Sat, 17 Sep 2022 10:57:49 GMT

UPDATE: Managed to get this

But I need it to show blanks as well as true and false, any idea how I could do that?

I'm also stuck here, the numbers aren't adding up to the actual pass results from my original table...

---

Hi tackytechtom my file is very large and connected to a sql database.

I can provide the calc that I used for my "Pass" / "Fail" measure:

I have tried using SUMMARIZE but can't figure out to swap out the number values with the values from my measure above

Re: Count Pass/Fail per month by KPI Priority Group (i.e. not individual jobs)

tamerj1 — Sat, 17 Sep 2022 10:59:38 GMT

Anonymous

Then try by iterating over the KPI Goup column. You need to iterate over the granularity level column that produces results for [MeasureResp]

Re: Count Pass/Fail per month by KPI Priority Group (i.e. not individual jobs)

Anonymous — Sun, 18 Sep 2022 03:16:13 GMT

Still blank 😕

This is my Measure that produces the result

Re: Count Pass/Fail per month by KPI Priority Group (i.e. not individual jobs)

Anonymous — Sun, 18 Sep 2022 03:59:38 GMT

Just tried something and looks like it's working! (in case anyone else is struggling with something similar)