cancel
Showing results for
Did you mean:  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).

 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.

AJ

1 ACCEPTED SOLUTION  Super User

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)
`````` Proud to be a Super User!

5 REPLIES 5  Super User

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

Proud to be a Super User!  Helper I

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

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)
`````` Proud to be a Super User!  Helper I

Hi Ryan, Could you please explain this calculated measure?

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

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

Proud to be a Super User! Announcements The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform. #### Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison! #### 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
Users online (1,731)