Forum Discussion
Countif ?
Hi RvdHeijden,
Based on my understanding, you want to get the second example result form the first one, right? If it is, I try to reproduce your scenario and get expected result.
Create measures using the following formulas.count = CALCULATE(COUNTA(Test1[Name]),ALLEXCEPT(Test1,Test1[Name])) Total sick days = CALCULATE(SUM(Test1[Total SickDays]),ALLEXCEPT(Test1,Test1[Name]))
Create a table visual, you will get the expected result.
Please let me know if you have any questions.
Best Regards,
Angelia
i used your option and it works fine but now i want to expand on that idea.
I now have a colum which has the number of times that person called in sick but in the original table i also have a colum with the number of sickdays that person has and i want to combine that as wel
Name #Total SickDays
Piet 5
Piet 3
Piet 12
So now i have a new table with Names (unique) and the times he/she called in sick and the new colum should have the combined number of sick days
for Example
Name #Sick #Total SickDays
Piet 3 20
Your previous solution got me Column 1 and 2 ('Name' and '#Sick') but now i want column 3 as wel (#Total SickDays), how do i do that ?
#Total SickDays is a calculated column not a column in the original data
I would use the employee names as a lookup table then sum the number of sick days used from table 1 and sum the number of sick days available from table 2 then place those measures on the visual using the employee name for the axis or row from the look up table.
Then, I would create a measure for remaining sick days.
Sick days used = SUM (Table1[DaysSick])
Sick days available = SUM(Table2[SickDaysAvailable])
Sick Days Remaining = [Sick days available]-[Sick days used]
Or you could group the number of days on a different table and merge it together if you just wanted to keep on fact table.