Sum of the employee average by category

[metcala]

Hi all

Any help on this would be very much appreciated....

I have the following data structure:

Period	Employee Number	Area	FTE	Category	Cost
1/1	101	Finance	1.0	A	$100
1/1	101	Finance	1.0	B	$50
1/1	102	Operations	0.8	A	$75
1/1	102	Operations	0.8	B	$30
1/1	103	Finance	1.0	A	$150
1/1	103	Finance	1.0	B	$75
...	....	...	...	...	...

And I am looking to be able to create the following:

Area	FTE	Cost
Finance	2.0	$375
Operations	0.8	$105

The FTE should be the sum of the average/first value for each employee for each area.

The cost should just be a sum of the total cost.

The cost is working absolutely fine but I am having some trouble with the measure required for the FTE.

This is what I have come up with so far....

SumFTE = CALCULATE(SUMX(DISTINCT(Table1[Employee Number]), FIRSTNONBLANK(Table1[FTE],0)))

Thanks in advance!

[DimaMD · Accepted solution]

hI @metcala Try it 

_FTE = 
SUMX( 
    SUMMARIZE( 
        'Таблиця', 
        'Таблиця'[Area], 
        'Таблиця'[Employee Number], 
        'Таблиця'[FTE]
    ), 
        [FTE]
)

_Cost = 
SUMX( 
    SUMMARIZE( 
        'Таблиця', 
        'Таблиця'[Area], 
        'Таблиця'[Employee Number],
        'Таблиця'[Cost]
    ), 
    [Cost]
)