cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ThomasN51
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! 

ThomasN51_0-1652458605704.png

 

18 REPLIES 18
v-kalyj-msft
Community Support
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)

 

Additionally, here are other solutions about this topic from blog:  Before You Post, Read This - Microsoft Power BI


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.

SpartaBI
Community Champion
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.

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.

ThomasN51_0-1652463152174.png

 

SpartaBI
Community Champion
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


2022-05-09 22_36_04-Power BI Workspace Datasets License Permissions - Microsoft Power BI Community.png

Showcase Report – Contoso By SpartaBI


SpartaBI_3-1652115470761.png  SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Full-Logo11.png

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.

SpartaBI
Community Champion
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)?

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

SpartaBI
Community Champion
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
   )
)

 


2022-05-09 22_36_04-Power BI Workspace Datasets License Permissions - Microsoft Power BI Community.png

Showcase Report – Contoso By SpartaBI


SpartaBI_3-1652115470761.png  SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Full-Logo11.png

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. 

SpartaBI
Community Champion
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

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.

ThomasN51_0-1652465708675.png

ThomasN51_1-1652465736499.png

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

ThomasN51_2-1652465920449.pngThomasN51_3-1652465942533.png

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

 

SpartaBI
Community Champion
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

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

SpartaBI
Community Champion
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

SpartaBI
Community Champion
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
   )
)

 


2022-05-09 22_36_04-Power BI Workspace Datasets License Permissions - Microsoft Power BI Community.png

Showcase Report – Contoso By SpartaBI


SpartaBI_3-1652115470761.png  SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Full-Logo11.png

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

SpartaBI
Community Champion
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

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!

ThomasN51_0-1652461066771.pngThomasN51_1-1652461130386.png

 

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors