Help with correction in the current measure

[vjnvinod]

Dear all,

Below pic represents what i have created in power BI, basically slicer input box grouped by rank

so that i can enter values to see the changes in the funnel chart

Now after entering value "3" in Expat_P text box, numbers changed in the table(seee below pic), however the number are not summing up in the funnel chart

Let me know what needs to be corrected in the below measure, so that when i enter values in the text box, my funnel chart updates with that number.

FY21 Scenario Closing Projection HC Expat_National =

VAR __raw = [Latest HC]+[Preboarding Pipeline]+[Additional approved Exp Hire]+[Additional Approved Graduate]-[Confirmed Exists]-[Exit Pipeline]

Var X=SELECTEDVALUE(Rank_1[Rank_new])

RETURN

SWITCH ( SELECTEDVALUE(Nationality[National/Expat_P] ),

"Expat", IF(X="Partner/Principal",[Expat_P Value],IF(X="Manager",[Expat_M Value],IF(X="Senior",[Expat_S Value],IF(X="Senior Manager",[Expat_SM Value],IF(X="Staff/Assistant",[Expat_S/A Value],IF(X="Executive Director",[Expat_ED Value],0)))))),

"National", IF(X="Partner/Principal",[National_P Value],IF(X="Manager",[National_M Value],IF(X="Senior",[National_S Value],IF(X="Senior Manager",[National_SM Value],IF(X="Staff/Assistant",[National_S/A Value],IF(X="Executive Director",[National_ED Value],0))))))

) + __raw

[vjnvinod]

Dear experts

@Anonymous  @parry2k   @amitchandak  @MFelix 

here is my link to the pbix (Sanitized one)

https://drive.google.com/file/d/1w5fn8ufvKaGWoPgWAewaV2N9rhSweFnx/view?usp=sharing

please let me know if there is a way i can achieve this

[Anonymous · Accepted solution]

Try using the following measure...

CorrectedMeasure =
VAR __raw = [Latest HC] + [Preboarding Pipeline] + [Additional approved Exp Hire] + [Additional Approved Graduate] - [Confirmed Exists] - [Exit Pipeline]
VAR X =
    SELECTEDVALUE ( Rank_1[Rank_new] )
VAR NEP =
    VALUES ( Nationality[National/Expat_P] )
VAR ExPVal =
    SWITCH (
        X,
        "Partner/Principal", [Expat_P Value],
        "Manager", [Expat_M Value],
        "Senior", [Expat_S Value],
        "Senior Manager", [Expat_SM Value],
        "Staff/Assistant", [Expat_S/A Value],
        "Executive Director", [Expat_ED Value],
        0
    )
VAR ExP =
    IF ( "Expat" IN NEP, ExPVal, 0 )
VAR NaPVal =
    SWITCH (
        X,
        "Partner/Principal", [National_P Value],
        "Manager", [National_M Value],
        "Senior", [National_S Value],
        "Senior Manager", [National_SM Value],
        "Staff/Assistant", [National_S/A Value],
        "Executive Director", [National_ED Value],
        0
    )
VAR NaP =
    IF ( "National" IN NEP, NaPVal, 0 )
VAR TR = __raw + ExP + NaP
RETURN
    TR

[Anonymous]

I changed the SWITCH() and IF() conditions slightly using the "IN" operator to accommodate testing of multiple values of Nationality in the Funnel chart. In the table, the current row always had only one value, therefore those if conditions worked. In funnel chart, it had multiple values depending upon the level at which the funnel is displaying the info. So we had to change the IF condition's single value check to a Multi-Value check using the IN operator.

Hope it works.

[vjnvinod]

@Anonymous 

thanks so much, worked like charm, appreciate your help on this