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.
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
Solved! Go to Solution.
@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)
Proud to be a 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.
Proud to be a Super User!
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!
@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)
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)
@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..
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |