cancel
Showing results for
Did you mean:
New Member

## How to count number of values from a measure that matches certain criteria?

Hi,

My model gather data from our manufacturing process. After analyzing the mean and standard deviation among other factors, a process capability index can be calculated, like the picture below. My question is how would I determine the fraction of CpK values that match certain criteria (i.e., >1.33). I have tried using COUNTX() function but that does not work on a measure. I have also tried creating another measure that returns "1" if CpK>1.33 and "0" otherwise, then try to sum up this new measure but could not get that too work as well. Any other ideas? Thank you very much!

18 REPLIES 18
Community Support

Hi @ThomasN51 ,

Have you tried create a measure

``Measure = SUMX('Beauty Making',IF([Cpk]>4,1))``

Or a calculated column

``Column = IF([Cpk]>4,1)``

## The totals in your table/matrix are wrong

This is almost certainly caused by what is referred to as “the measure totals problem”. This one is extremely common. See this post that explains it, Dealing with Measure Totals

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need.

Matrix Measure Total Triple Threat Rock & Roll (MM3TR&R) can also be useful.

Or this Quick Measure submission, Table Matrix Totals or Subtotals

Hope it helps!

Best Regards,
Community Support Team _ kalyj

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

Community Champion

@ThomasN51 on the picture of the table visual (bottom) are the more columns on the left? All I see are explicit or implicit measure but there must be a column on the rows of that table or matrix. That is the column I'm looking for - what I referred as attribute in my function.

New Member

The table itself was too big so I had to screenshot the left and right portion separately. The table starts with the column "Material" and ends with column "GCAS_safe." I don't fully understand your statement. Starting with the average, everything to the right of that column are measures and involves calculation. The first few column are just columns from my data table. I took your suggestion and input the measure below. "Beauty Making" is the name of my table and "Material" is the first column. You can see what the result looks like when that measure is added to the table. If I create a separate table, "GCAS_Safe" still only show a value of 1.

Community Champion

@ThomasN51 OK, so my measure worked from the start :))
If you look at the total of that column this is the number you wanted no?
Also, you can just put that measure in a card visual next to your table visual.
On the row level it will be 1 if the condition is met and blank if not, and on the totals it's the count of all the materials that surpess 1.33. I thought that's what you wanted.
If you want to show a 0 instead of blank just add  + 0 to the end of the measure

Showcase Report – Contoso By SpartaBI

New Member

For some reason, the column total only show the value of 2. And when I put that measure on a separate card visual, it only returns value of 1.

Community Champion

@ThomasN51 so I suspect there are more colulmn in play here and not just the material. What is the chance you share your model here (the PBIX in dropbox or some shared drive)?

New Member

There are definitely columns from my data table that I omit from this report. So you are saying I need to include all of them in order for that measure to work properly? And sorry I won't be able to share my model beyond those screenshots

Community Champion

@ThomasN51 yes, if there are more columns that contribute to the result of each row you need to consider them in the total measure.
Something like this:

``````Incidents =
COUNTROWS (
FILTER(
SUMMARIZE(
'Table',
'Table'[Attribute1],
'Table'[Attribute2]
),
[CpK] > 1.33
)
)``````

Showcase Report – Contoso By SpartaBI

New Member

Including the SUMMARIZE() into the measure is helpful and it seems like the total column is actually producing a meaningful number but not exactly what I want. Sometimes the measure gives result of 0 even though my index is >1.33. I would like to ask for clarification on what columns to include. What do you mean exactly by "contribute to the result of each row"? The index is calculated based on recorded measurements, and all of these data comes from a single column. Other columns from my data table only expands on the product and testing info.

Community Champion

@ThomasN51 show me the measure you wrote and the results you are getting. Maybe try to remove all other measures so you could share the entire visual in a screenshot

New Member

This is what the visual looks like with the VALUES() function inside. Playing around with this for a bit, "Material" is the only column that produces good result where CpK>1.33 gives a 1 and CpK<1.33 gives a 0, the column total are just not summing up correctly.

This is what it looks like if I include the SUMMARIZE() function

I chose these two columns specifically because "Material" specifies the product, while "Result" specifies the data from which I calculated the indices

Community Champion

@ThomasN51 strange 😞 Hard to say without looking at your measure.
Let's try this:

``````GCAS Safe =
SUMX (
FILTER(
VALUES('Beauty Making'[Material]),
[CpK] > 1.33
), 1
)``````

Another options is also add the mateiral description column to the summarize function from before

New Member

That SUMX() function was actually one of my earlier approach. Again, I encountered the problem outlined in my original question. Getting that to show 1 or 0 depending on the value of the indices is not hard. But none of the methods I have tried can give the correct column total in the bottom.

Reading into this further, seems like Power BI does not just sum up all the values of a measure for the total, but instead evaluate the measure one last time based on the total data. I have not found any workaround on the forum that is applicable for my case unfortunately

Community Champion

@ThomasN51 I'm sure this could be solved but I ran out of ideas without looking into the actual model together with you on a zoom. Maybe someone else could figure this out without it. Let's wait for other members to join this thread

Community Champion

@ThomasN51 I see some rows of the same measure. That's mean you have something on those rows in the matrix the does that split, let's call it column [Attribute] and let's say it is in a table named 'Table'.
Your measure will be as follows:

``````Incidents =
COUNTROWS (
FILTER(
VALUES('Table'[Attribute]),
[CpK] > 1.33
)
)``````

Showcase Report – Contoso By SpartaBI

New Member

Thank you for replying! However, when I implemented your suggestion, the "Incidents" measure always return value of 1 instead of the number that exceeds 1.33 like I wanted

Community Champion

@ThomasN51 can you share more details about your model table relevant columns in the desired visual and where do you want show that result and on which visual

New Member

Attached is what my table currently looks like. I will work on more visual later after we finish with all the numbers. Essentially, each product is tested for the correct specifications prior to release. The process capability index, CpK, is calculated using a formula taking in average, std dev, and specification limits of the product. As you can see, I'm able to report the index for each product with respect to the characteristic. Now what I'm looking for is a measure of that gauge how much of the indicies are "healthy" (i.e., >1.33). This percentage can in its own table or as a column on the existing table, I don't really care. I hope this provides clarification on what I'm looking for. Thank you!

Announcements