FTE, aggregate as sum on all dimensions except date. On date it should be average

[jvandyck]

An extract of the data looks like this:

sk_datum	fte	sk_management	SK_Medewerker
20210331	1	618	14493
20210430	1	618	14493
20210531	1	618	14493
20210228	1	618	14493
20191031	1	618	14493
20191130	1	618	14493
20191231	1	618	14493
20200131	1	618	14493
20200229	1	618	14493
20200831	1	618	14493

The last 2 columns link to an employee dimension, and an org chart.

On these 2 dimensions, I would like to sum the FTE's. However on the date dimension, I would like to aggregate by average.

For your information, the date column will always contain one date value per month.

[amitchandak · Accepted solution]

@jvandyck , one of the ways. have a measure like this

averageX(values(Date[Date]), [Measure])

[jvandyck]

I did investigate further and got it to work now. My first try was the following formula:

FTE = AVERAGEX(values(Datum[Datum]), sum(FTE[teller])

This did not work.

My next try was 

FTE = AVERAGEX(values(Datum[Datum]), [Total FTE])

This does work! The difference is that total fte is a calculated measure on its own: sum(FTE[teller].

So what I did not interpret well, was that the sum aggregation of FTE had to happen in a calculated measure. Not sure I fully understand, but it probably has to do with the sequence of the calculations.

[jvandyck]

Thanks for your quick reply. I tried your suggestion but it does not seem to work. Here you can find a mock-up: https://www.dropbox.com/s/udqez5pngu2x5wn/Thuiswerk%20test.pbix?dl=0