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.
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]