Create table with year and summary and employee ID

[Jill_Mc]

Hi Community,

I have employee data for multiple years, with employees duplicated across years that they have had a paper published.  I need to aggregate the data for each year for each employee and put the total in a column chart. We have to add up data for the previous 3 years and ignore any that are older than 2021.

Starting dataset:

Semantic model example:

Employee ID	Department	Year	Publications	Income	Students
ABCD	Physics	2020	3	$6000	1
EFGH	Art	2021	4	$1500	4
ABCD	Physics	2021	10	$3000	7
QRST	Math	2022	5	$500	2
ABCD	Physics	2022	1	$800	2
UVWX	History	2023	4	$3000	1

I would like to get a table out that

1. de-duplicates the Employee ID and

2. adds up their metrics for each included year

3. Gives me a total output for all included years

4. Doesn't give me anything older than 3 years ago

Example table out:

Employee ID	Department	Total Publications (last 3 years)	Total Income (last 3 years)	Total Students (last 3 years)
ABCD	Physics	11	$3800	9
EFGH	Art	4	$1500	4
QRST	Math	5	$500	2
UVWX	History	4	$3000	1

I am new to Power BI and need some help with this, I am nearly finished my report, but this is a sticking point.

Thank you from Australia.

[muhammad_786_1 · Accepted solution]

Hi, 

You can use these measures to achieve your desired output.

TotalPublications = 
CALCULATE(
    SUM('Table'[Publications]),
    FILTER('Table', 'Table'[Year] >= 2021)
)
TotalIncome = 
CALCULATE(
    SUM('Table'[Income]),
    FILTER('Table', 'Table'[Year] >= 2021)
)
TotalStudents = 
CALCULATE(
    SUM('Table'[Students]),
    FILTER('Table', 'Table'[Year] >= 2021)
)

Best Regards,
Muhammad Yousaf

If this post helps, then please consider "Accept it as the solution" to help the other members find it more quickly.

LinkedIn

[VahidDM · Accepted solution]

Hi @Jill_Mc 

Add all columns inot a table and set aggeragation for each column, change their name and add Year to the filter section with >=2021, Output:

sample file attached for your reference

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!! 

LinkedIn|Twitter|Blog |YouTube 

[muhammad_786_1 · Accepted solution]

Hi, 

You can use these measures to achieve your desired output.

TotalPublications = 
CALCULATE(
    SUM('Table'[Publications]),
    FILTER('Table', 'Table'[Year] >= 2021)
)
TotalIncome = 
CALCULATE(
    SUM('Table'[Income]),
    FILTER('Table', 'Table'[Year] >= 2021)
)
TotalStudents = 
CALCULATE(
    SUM('Table'[Students]),
    FILTER('Table', 'Table'[Year] >= 2021)
)

Best Regards,
Muhammad Yousaf

If this post helps, then please consider "Accept it as the solution" to help the other members find it more quickly.

LinkedIn