How to Exclude the count which is already counted

Question & Answer
1

Hello Team,

Could some one please assist me on below issue.

I have a dataset as looks like below.

image

Below is the formula which I have return to get the category count based on the severity. Please assist how to achieve this.

distinct_countIf({category},{status}=“Fail” AND {severityname}=“2”) , result shows 2.

But actually in category A we have 3 defects with severity as 1, 2 and 3 . 3 will be the Major one.

If category has multiple exceptions from different severity levels, the category is included in the calculation based on the highest severity level noted for that category.

2

Hi @tdr_Dinesh Based on the formula you have shared and the documentation for the formula of distinctcountif here: distinct_countIf - Amazon QuickSight, the result of 2 is appropriate.

Explanation: in your formula, you’re counting the distinct number of categories after applying the conditions of “status” = “Fail” and “Severity” = “2”, which leads to the categories of A and D in the sample dataset, hence the output of 2.

If this is not what you’re intending to achieve, please feel free to explain with an example what your desired outcome is, so the community members can help. Thanks.

3

Hi @SD_QS ,

Thanks for your prompt response.

I am expecting the result as “1”.

In category “A” we have highest severity level as “3” when I have applied the formula for category count based on severity 3, result shows “1” which is correct.

Now, when I calculate the category count for severity level 2, category A has to be exclude. Since I have already taken the highest severity level from category A. please assist.

4

@tdr_Dinesh Is this the final output you’re expecting (first 2 columns; the last column is a comment…)? Ofcourse this is considering the “Fail” status only…

image

1 Like
5

Hi @SD_QS ,

Yes , that’s correct.

6

@tdr_Dinesh Here is one way you can do it:

I have created 2 calculated fields:

  1. maxSev = maxOver(Severity,[Category],PRE_FILTER)
  2. distinctCount = distinct_countIf(Category, Status = “Fail”)

I have made the input data slightly more complex; the raw data and the outputs are below:

image
image892×363 9.38 KB

Let us know if this does the trick for you? Thanks.

1 Like