KPI for aggregated values

Hello!

I’ve got a simple excel, one column:

Person
Adam
Adam
John
Mark
Mark

5 records, 3 unique values.

I want to have a KPI that shows how many people appeared at least twice in the column (the value should read: “2”).
I wanted to got with a simple count({Person}) first and than create additional calculation for filtering, like:
ifelse({count of people}>=2,‘here’, ‘not here’)
and use it as a filter for the KPI. It does not work:

What is the solution for such a case? I tried LOD calculations, but they did not seem to work (?)

Thanks a lot!

Hello @Filo and thanks for posting your question.
One possibility is to make use of the calculated field you already created (count({Person})) and apply a filter on the KPI visual.
Particularly:

  1. Select the Filter option on the left side ribbon
  2. Add Filter and select the desired field (Person)
  3. Click on the 3 dots on the right of the newly created filter and edit it
  4. Making sure the ‘Include’ Filter condition is ticked, select the Add filter condition
    → select the calculated field (count of people) as field to filter
    → select the ‘Greater than or equal to’ Filter condition
    → set 2 as minimum value and click on APPLY
  5. Lastly, make sure to apply this filter on the KPI visual you built

Please let me know if this solved your problem :slight_smile:

Hello!
Thanks for reaching out, but either I don’t apply it correctly or it does not seem to work. Count of person filter works only if greater than 5 (filtering all out, this makes sense for me as the KPI lacks Person dimension and i don’t know how to add it there)

I created a gallery version of the issue: Aggregated test

The only way (?) I can add the filter is:

  1. Select visual
  2. Click “add filter” on filter pane
  3. Select column for the source of the filter
  4. Edit filter

But then I land in the same spot.

This approach works for the table:

And this is because, I suppose, we have “person” dimension in the table.
In the other BI tool for a KPI I would do the filtering. How do I achieve the result in QS?

Hello @Filo and @mzerilli !

@Filo were you able to find a solution or workaround for this or are you still facing this issue?

Ultimately, this would be easier if you had another field to partition the data by like a user id or other unique attribute, for example if you create the count calculation in the query you could use that as the attribute and not run into the nesting aggregation errors.

Hello @Filo and @mzerilli !

It has been some time since we have heard from you but would still like to help you find a solution. If we do not hear from you in the next 2 business days this topic will be archived.