Wrong values displayed in bar chart and KPI (again)

[lailarhc]

Hello

I've posted something like this before, as seen here: Wrong values displayed in bar chart 

But now I'm working in a similar but different project and the solution given in the above question isn't working for me.

I have a table like this:

In which the dimensions are, in order of column, from left to right: [Nome do servidor], [Data.autoCalendar.Year], [Data.autoCalendar.Month], [Nome Evento], [Carga Horária], [Área de Concentração]

Here's my bar chart:

Here's my expression for the KPI:

(Count({$} Aggr(distinct [Nome Evento], [Nome do servidor], [Data.autoCalendar.Year], [Data.autoCalendar.Month], [Carga Horária])))

Here's my expression for the bar chart:

(Count({$} Aggr(distinct [Nome do servidor], [Data.autoCalendar.Year], [Data.autoCalendar.Month], [Nome Evento], [Carga Horária])))

There are both month and year filters selected. The bar on the chart should display the number of records in the table: 13

But it's displaying a much lower value.

Also, the value is much higher than it should be when I use the following expression:

Sum({$} Aggr(Count(distinct [Nome Evento]), [Nome do servidor], [Data.autoCalendar.Year], [Data.autoCalendar.Month], [Carga Horária], [Área de Concentração]))

Why is that? How do I make it display the correct value?

[Or]

I'm struggling to follow your logic here, and these formulas don't really help. What are you trying to display in the KPI, exactly? What is the Nome Evento field (it's not in the table you posted)? Why are you using aggr() with no aggregation function inside?

[lailarhc]

It's in the dimensions used for the table.
[Nome Evento] =  Curso

In the KPI I'm trying to display the number of courses (Nome Evento) taken by all selected employees (Nome do Servidor) in all the years selected

And maybe I understood the aggr function wrong, but isn't it supposed to basically create an imaginary table with all the parameters as columns where you can perform aggregation functions using the lines of this imaginary table? 

For example, I want to count the number of lines that there are in a table that has A, B and C as columns. So I'd use Count(Aggr(A, B, C))

Or at least that's how I understood. Am I wrong? If so, how would I do the above?

[Or]

Typically if you want to count the number of lines in a table, you just count(Field) where Field is from that table (and has no null values). You don't need to set up an aggr() structure for that. In your case, it sounds like you just need to count([Nome Evento]), since the selection of years and employees will automatically reduce the data being counted. If you're just looking for the number of courses (rather than the number of lines in the table), you'd count (distinct [Nome Evento]) instead.

Keep in mind that for aggr(), the first parameter is what you are aggregating and the additional parameters are what you are aggregating by. So for example aggr(A,B,C) would aggregate Only(A) by B and C (only is implicit if you don't provide your own aggregation) and would return values only for cases where a combination of B and C contains a single distinct value of A.

[lailarhc]

But I don't want to count how many [Nome Evento] exists or how many employees ([Nome do Servidor]) exist. I need to count how many courses were taken by different employees in a set period of time. And some employees may have taken the same course.

[Or]

That still sounds like count(distinct [Nome Evento]) to me - you count the number of distinct courses that appear within the selection (which you said was years and employees). Courses not taken by any employees will not appear because a selection has been made on employees, meaning all courses with no employees associated with them during the selected time frame should be filtered out.

[lailarhc]

If I use the expression Count(distinct [Nome Evento]) in both the bar chart and the KPI, it says 74 courses were taken in october in the year of 2021:

But with the same filters applied, here's what the table looks like:

There's only 7 entries in the table. Where are the 74 courses? Who did them?

How can I make the table match the bar and the KPI? I want the table to show me the details of all the courses being counted in the bar and the KPI.

How can I do that?

[Or]

I don't know what your actual data is, but if you add the expression count(distinct [Nome Evento]) to your table, it should be 1 for every line if Nome Evento is indeed unique to a course as you suggest it is. If you get another result, that means Nome Evento isn't unique to a course and you should be counting something else (the course's unique ID, or whatever).

You should also make sure that your table isn't suppressing any data. There may be courses which appear in the dataset but which are suppressed in the table for whatever reason - again, without access to the data and table itself, I have no way of knowing.

Count(Distinct [Nome Evento]) should match the number of Nome Evento you can count by hand if you look at the Nome Evento field individually (through the Selections screen, for example) after making your selections.

[lailarhc]

Some employees have taken the same course and some courses have gone on for more than one day, meaning that in the table there are entries in which the name of the course (Nome Evento), employee, year, month and category ([Área de Concentração]) are the same but the number of hours for the duration of the course (Carga Horaria) is different.

Here's an example:

But that shows on the table.

What more do you need to know to help me? Would sending the table data help? I can do that (like, exporting the table data in a excel file I mean).

Also, if I add Count(distinct [Nome Evento]) to my table as a measure it still doesn't match the numbers in the KPI and the bar chart.
Here's an example with the filters for the year 2021 and the month of october applied:

As you can see, it shows only one of each course above.

And the bar chart/KPI look like this:

I don't understand where the number 74 is coming from.

[Or]

When you count (distinct [Nome Evento]), it doesn't matter if that repeats for multiple rows - you're only counting the number of distinct values. If there is a 1:1 relationship between Nome Evento and the number of courses, this should return the correct value. You can check this by creating a simple table where Nome Evento is the dimension and count(distinct [Nome Evento]) is the expression - you should get the same number of lines as the KPI object and the expression should always evaluate to 1.