Dynamix Measure

Sagittariu5

New member
Joined
Sep 22, 2020
Messages
11
Reaction score
0
Points
0
Excel Version(s)
2016
Hello,

Hoping someone could help.
I'm trying to find a way to shorten this measure, as it's based on the position and most the of filtered columns are repeated.

Code:
[COLOR=#000000][FONT=Consolas]Gross Issued APV (Team) = 
VAR HierarchySel =
    SELECTEDVALUE ( 'Hierarchy'[Hierarchy Switch] )
VAR SelectedCode = [Agent Code]
VAR SelectedDiv =
    SELECTEDVALUE ( 'Employee Selection'[Division] )
VAR SelectedReg =
    SELECTEDVALUE ( 'Employee Selection'[Region] )
VAR SelectedPos =
    SELECTEDVALUE ( 'Employees by Week'[Position (by Week)] )
VAR SUMMARIZEPos =
    SWITCH (
        SelectedPos,
        "DM", COUNTROWS ( SUMMARIZE ( 'Employee Selection', [Divisional Manager] ) ),
        "RM", COUNTROWS ( SUMMARIZE ( 'Employee Selection', [Regional Manager] ) ),
        "DL", COUNTROWS ( SUMMARIZE ( 'Employee Selection', [District Leader] ) ),
        "SL", COUNTROWS ( SUMMARIZE ( 'Employee Selection', [Sales Leader] ) ),
        0
    )
VAR DM_APV =
    CALCULATE (
        [Gross Issued APV],
        'Hierarchy'[Hierarchy Switch] = HierarchySel,
        'Employee Selection'[Division] = SelectedDiv,
        ALLEXCEPT ( 'Employee Selection', 'Employee Selection'[Divisional Manager] ),
        NOT 'Employee Selection'[Agent Code] IN { SelectedCode, "HHHH" }
    )
VAR RM_APV =
    CALCULATE (
        [Gross Issued APV],
        'Hierarchy'[Hierarchy Switch] = HierarchySel,
        'Employee Selection'[Division] = SelectedDiv,
        'Employee Selection'[Region] = SelectedReg,
        ALLEXCEPT ( 'Employee Selection', 'Employee Selection'[Regional Manager] ),
        NOT 'Employee Selection'[Agent Code] IN { SelectedCode, "HHHH" }
    )
VAR DL_APV =
    CALCULATE (
        [Gross Issued APV],
        'Hierarchy'[Hierarchy Switch] = HierarchySel,
        'Employee Selection'[Division] = SelectedDiv,
        'Employee Selection'[Region] = SelectedReg,
        ALLEXCEPT ( 'Employee Selection', 'Employee Selection'[District Leader] ),
        NOT 'Employee Selection'[Agent Code] IN { SelectedCode, "HHHH" }
    )
VAR SL_APV =
    CALCULATE (
        [Gross Issued APV],
        'Hierarchy'[Hierarchy Switch] = HierarchySel,
        'Employee Selection'[Division] = SelectedDiv,
        'Employee Selection'[Region] = SelectedReg,
        ALLEXCEPT ( 'Employee Selection', 'Employee Selection'[Sales Leader] ),
        NOT 'Employee Selection'[Agent Code] IN { SelectedCode, "HHHH" }
    )
VAR GrossAPVPos =
    SWITCH (
        SelectedPos,
        "DM", DM_APV,
        "RM", RM_APV,
        "DL", DL_APV,
        "SL", SL_APV,
        0
    )
VAR WithinSelection =
    IF ( SUMMARIZEPos > 0, TRUE () )
RETURN
[/FONT][/COLOR]
[COLOR=#000000][FONT=Consolas]    IF ( WithinSelection, GrossAPVPos, BLANK () )[/FONT][/COLOR]
 
Back
Top