cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ashishmishra
Helper I
Helper I

Subtracting a cell value and Find difference of a Calculated Column

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).

 

QuarterDepartmentCount of employeeCount of Manager% Manager%QoQ
Q4XYZ751520%-
Q1XYZ1002222%2%
Q2XYZ1253528%6%

 

Any help would be much appreciated here.

 

Thanks in Advance,

AJ

1 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)

111.PNG

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

5 REPLIES 5
ryan_mayu
Super User
Super User

@ashishmishra 

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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

QuarterDepartmentCount of employeeCount of Manager% Manager%QoQ
FY21 Q1XYZ751520%-
FY21 Q2XYZ1002222%2%
FY21 Q3XYZ1253528%6%
FY21 Q4XYZ1554529%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!

@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)

111.PNG

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hi Ryan, Could you please explain this calculated measure? 

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

 

@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..





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors