Analyse by dimension and produce report based on threshold

[MahmudSaniat]

Hello,

I have employee data in this format:

Employee	Department	Country	Date	Movement Type
100	IT	Germany	31/01/2022	Hire
100	IT	Germany	28/02/2022	Headcount
100	IT	Germany	31/03/2022	Promotion
100	IT	Germany	30/04/2022	Leaver

From this information, I want to generate an analysis like:

Department	Promotion Rate	Hire Rate
IT	20%	5%
HR	10%	10%
Commercial	30%	7%

In the above dataset, I have only two dimensions: Department and Country.

However, in real data, I will have 20+ dimensions, and also will have trend data over multiple years.

I am wishing for a model which will provide a list of spikes (e.g. above or bellow X%) like:

1. In Oct 2021, there was a +7% spike in Hires, for Job Level = Vice President, Country = Netherlands.
2. In Sep 2020, there was a -3% spike in Promotions for Department = Commercial.

This means in the background, the model needs to run all the combination of Dimensions against all the Metrics I have throughout the timeline.

I am not expecting anyone to build the model for me, rather asking for advise with some direction.

Thanks.

[dougperez]

Well, that's a good question...

I would create that "by hand" transposing the values ​​and creating one analysis by one, using the multi-field formula... Perhaps there be a "easy" way to do that, but I don't know