Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
im trying to get my master item measure correct but im having trouble coming up with the correct formula.
my current formula gets the sum of my row counts / sum of my failure counts:
sum([Failure Mode Count]) / count(distinct[Record Count])
however the issue here is that if my row count is 1 but within that row i have multiple failures, the % will be wrong.
what i need is regardless of how many failures on a row, it will only show as 1 fail on a row max. or zero if no fails obviously
im puling in numerous csv files and im analyzing each row/value.
so my row count = total number of rows on all my csv files (based on selection)
failure count = total number of a specific value while searching all value .
is there a way to do the above? where if a row has a failure count, the count is 1, regardless of how many fails in that row?
many thanks
try this use TOTAL :
sum([Failure Mode Count]) / sum( Total [Record Count])
Thanks
Vikas
hi @vikasmahajan , the issue i think is with the failure mode count, i dont want more than one failure on each record count,
getting a total of my record count doesnt do the trick for me.
e.g. if my record count is 10, that means i have 10 rows of data, each row can contain up to 50 values.
if row 5 has a value that is a failure, that failure mode for that row = 1,
if row 6 has 2 values that is a fail, that failure mode is still 1.
then ideally i can do a total sum/count of my records and my fails to get a % of number of rows with 1 or more fails
thanks
Have you try this ?
sum( TOTAL [Failure Mode Count]) / sum( [Record Count])
Vikas
@vikasmahajan yes the total function does not work.
using that function i get a value of 2 for my failure count when i have it filtered down to a specific row, whereas i want a value of 1, even tho there are 2 failures on that row:
for reference, i also have a compound unique key generated for each row if that helps with the calculation.