Calculate Dynamic Rolloing Total Based on Slicer

[adieball]

Hi all

I have two tables

Training: contains name, email address, country and data relevant to whether a person has taken the training, exam, etc

AllStaff: contains name, email address, country and department of ALL users

That means, the Table "training" does not contain all possible users, only those who have registered for the training, participated, taken the exam, etc)

I then created a relationship between the two tables based on "email address"

From Table "Training" I have a slicer which is using the "country" field.

From Table "AllStaff" I have a Card which should show the count of email from "All Staff" based on slicer value

When no country is chosen, the card shows the correct value, but when I chose a country I get a value which is unclear to me.

E.g. I choose Germany

The number of entries in "Training" having country set to Germany is 46

The number of lines from "AllStaff" having country set as Germany is 125

But the card shows 36

Same is true when I choose different countries.

This is just a first step. Finally I want cards that show the percentage of total users from a country and given department that have already taken the training or passed the exam, but the first simple count doesn't work.

I'm sure I'm just overseeing soimething very obvious.

Any help is appreciated.

[Anonymous]

Hi,

Based on what you have written, I have not understood the reason behind creating the "Country" slicer based on "Training" table. Because "AllStaff" is likely to have unique values. But in Training table, If one staff had opted for multiple training, exams etc..., this table will likely to have multiple entries per staff.

Therefore, assuming that you have created the relationships correctly between these two tables and if there are 125 Staffs from Germany and out of which 36 of them had participated in any training or exams, but some of the staffs have taken more than one training / exam because of which the Training table shows 46 entries. In this scenario, choosing the "Germany" will not give 36 as a the result because it is the number of unique Staffs in Training table for which master is available in the "AllStaff" table.

If you want 46 as the result, then you must link the card to Training table so that it will count the duplicate entries also. If you want number of unique users from Germany who wave participated in training or exam, then you have to link the card to "AllStaff" table. 

The choice is up to you. I don't see any other reason for the unintended output.

[adieball]

Hi

"Training" has one unique entry per employee, so does "AllStaff"