How to calculate Net amount based on 2 values

[mdelalla]

Hi,

I am trying to calculate the net promotion amount based on those that move from a compensation class to another compensation class (NET PROMOTION = Total amount of employees that moved into the compensation class - Total amount of employees that moved out of the compensation class). For example, if we have a total of 50 employees that moved into CC02 and we have a total of 30 employees that moved out of CC02, then the Net promotion amount would be 50-30=20.

How can I do this so that I can show a matrix per year based on the Comp Class?

Test_Net Promotions.pbix

Thanks!

Regards

Marta

[mdelalla]

@Ashish_Mathurwould you be able to help me with my query above? So far I haven't received any responses and you were really helpful with a previous query I had in the past. Thanks!

Regards

Marta

[Ashish_Mathur]

Hi,

I am not clear about your expected result.  Share a smaller dataset and clearly show the expected result for that dataset.

[mdelalla]

Hi Ashish, you can find a smaller sample test in the link below.

https://1drv.ms/u/s!AoGWrXy4WRiC-3sWjw9LeD6j3NWv?e=ZFFAx7

This is an example of the table that what I want to create in Power BI

Comp Class	2018	2019	2020	2021	2022
CC04	2	0	0	0	4
CC03	1	2	5	6	7
CC02	6	4	0	7	2
CC01	-1	-5	-4	-7	-6

So for CC02 in 2018 there are 6 net promotions because there were 9 employees with New Comp Class as CC02 - 3 employees with Old Comp Class as CC02 = 6

Hope this is clear. Thanks for your help in advance!

Regards

Marta

[Ahmedx · Accepted solution]

pls try Sample PBIX file attached
https://1drv.ms/u/s!AiUZ0Ws7G26RinXGZEeFFuutdQlR?e=8e38f7

[mdelalla]

Thanks so much! this has worked perfectly! Really appreciate the help 🙂

Marta

[Ashish_Mathur]

You are replying to this thread after more than a month.  I just do not know what you want.  I lost the plot.