Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply

TopN 1 VS TopN 1 Previous Quarter

Dear Community, 

 

Hope my message finds you well and healthy!

 

I need to calculate the top 1 manager in a quarterly team race and to compare it with the top 1 manager from the previous quarter. 

As of now I was able to find the name with the following code (HRM stands for Highest Ranking Manager):

Atanas_Atanasov_0-1656338528254.png

HRM =
CALCULATE(
MAX(
'Test Dataset'[Manager Email]
),
TOPN(
1,
ALL(
'Test Dataset'[Manager Email]
),
[Coefficient],
DESC
),
VALUES(
'Test Dataset'[Manager Email]
)
)

Coefficient is nothing but Opps divided by Empl Number.

Furthermore I have calculated HRM LQ (highest ranking manager last quarter) with the following code:

Atanas_Atanasov_1-1656338665579.png

HRM LQ =

IF(
[Coefficient] <= 0,
BLANK(),
CALCULATE(
[HRM],
DATEADD(
'Fiscal Calendar'[Date],
-1,
QUARTER
)
)
)
and the condition for the New Coeff is:
New Coeff =
IF(
[HRM] = [HRM LQ],
DIVIDE(
[Coefficient],
2,
BLANK()
),
[Coefficient]
),
However, as it can be seen on the screenshot below it divides 3 out of 4 values by 2:
Atanas_Atanasov_2-1656338767963.png

While the only one I need to be divided is Coefficient = 23.

 

Can anyone help me with that? 

Thank you in advance, 

Atanas

1 ACCEPTED SOLUTION
johnt75
Super User
Super User

You need to add a check to make sure that [HRM] is not blank, 

New Coeff =
VAR currentHRM = [HRM]
RETURN
    IF (
        NOT ( ISBLANK ( currentHRM ) )
            && currentHRM = [HRM LQ],
        DIVIDE ( [Coefficient], 2, BLANK () ),
        [Coefficient]
    )

View solution in original post

2 REPLIES 2
johnt75
Super User
Super User

You need to add a check to make sure that [HRM] is not blank, 

New Coeff =
VAR currentHRM = [HRM]
RETURN
    IF (
        NOT ( ISBLANK ( currentHRM ) )
            && currentHRM = [HRM LQ],
        DIVIDE ( [Coefficient], 2, BLANK () ),
        [Coefficient]
    )

@johnt75 - Thank you so much for your help, it works like a charm! You made my day! Wish you all the best. 

All due respect,

Atanas

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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