Subtracting a cell value and Find difference of a Calculated Column

[ashishmishra]

Hi Guys,

Could you please help me here? This is my table and needs to calculate the %QoQ based on the difference of calculated column % Manager (i.e. Count of Manager/Count of employee).

Quarter	Department	Count of employee	Count of Manager	% Manager	%QoQ
Q4	XYZ	75	15	20%	-
Q1	XYZ	100	22	22%	2%
Q2	XYZ	125	35	28%	6%

Any help would be much appreciated here.

Thanks in Advance,

AJ

[ryan_mayu]

@ashishmishra 

do you have a year column? otherwise, if you have more than one year data, then you will have duplicated quarter values.

[ashishmishra]

Hi @ryan_mayu  I have data only for one year. Here is my table:-

Quarter	Department	Count of employee	Count of Manager	% Manager	%QoQ
FY21 Q1	XYZ	75	15	20%	-
FY21 Q2	XYZ	100	22	22%	2%
FY21 Q3	XYZ	125	35	28%	6%
FY21 Q4	XYZ	155	45	29%	1%

I need to calculate the %QoQ based on the difference of calculated column % Manager (i.e. Count of Manager/Count of employee).

Thanks for your reply!

[ryan_mayu · Accepted solution]

@ashishmishra 

maybe you can try to create an index column

index = mid('Table (2)'[Quarter],3,2)*100+right('Table (2)'[Quarter],1)

then create %QOQ

QOQ = 
VAR _last=maxx(FILTER('Table (2)','Table (2)'[index]<EARLIER('Table (2)'[index])),'Table (2)'[index])
VAR _lastmgr=maxx(FILTER('Table (2)','Table (2)'[index]=_last),'Table (2)'[% Manager])
return if(ISBLANK(_last),BLANK(),'Table (2)'[% Manager]-_lastmgr)

[ashishmishra]

Hi Ryan, Could you please explain this calculated measure? 

index = mid('Table (2)'[Quarter],3,2)*100+right('Table (2)'[Quarter],1)

[ryan_mayu]

@ashishmishra 

this is a calculated column to create an index for your data. It's because you don't have date column, then we can't sort your data in correct order.

get the two numbers after FY, which is 21, then *100, to be 2100

then get quarter and add quarter to the number we just calculated, which will be 2101, 2102, 2103, etc..