cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Anonymous
Not applicable

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

@Anonymous

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

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

Proud to be a Super User!

5 REPLIES 5
Super User

@Anonymous

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!

Anonymous
Not applicable

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

@Anonymous

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

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

Proud to be a Super User!

Anonymous
Not applicable

Hi Ryan, Could you please explain this calculated measure?

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

Super User

@Anonymous

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!

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors