I have the following measures for a list of teams
Team | Contract hours | Flexteam hours worked for team |
---|---|---|
Team A | 10 | 3 |
Team B | 12 | 2 |
Team C | 8 | 1 |
Flexteam | 9 | 1 |
Problem with this data is that there is a single team, ‘Flexteam’, that works its hours for other teams as shown in the second measure. The question at hand is to show the hours worked for the given team. The definition of [Hours worked for team]
is [Contract hours]
plus a correction for these ‘flexteam hours’.
The correction is as follows: [Flexteam hours worked for team]
for all Teams
except Flexteam. Flexteam gets the negative sum of [Flexteam hours worked for team]
except for own [Flexteam hours worked for team]
. This means that the hours worked for Flexteam by Flexteam would be ignored.
The result would be as follows:
Team | Correction flexteam | Hours worked for team |
---|---|---|
Team A | 3 | 13 |
Team B | 2 | 14 |
Team C | 1 | 9 |
Flexteam | -6 | 3 |
I have defined a measure that is able to show me all [Flexteam hours worked for team]
except on ‘Flexteam’:
[Flexteam hours worked except Flexteam] =
CALCULATE(
[Flexteam hours worked for team],
FILTER(Team, Team[Team] <> "Flexteam")
)
However I can’t get a measure to show me the negative sum of [Flexteam hours worked except Flexteam]
on ‘Flexteam’. I have tried the formula that follows, I have tried nested and a single CALCULATE()
, given KEEPFILTERS()
a try but haven’t gotten the desired result.
[Flexteam hours worked except Flexteam negative sum on Flexteam] =
CALCULATE(
CALCULATE(
[Flexteam hours worked except Flexteam]*-1,
ALL(Team)
),
FILTER(Team, [Team]="Flexteam")
)
These get me the following, -6 should only be listed for Flexteam.
Team | Flexteam hours worked except Flexteam | Flexteam hours worked except Flexteam negative sum on Flexteam |
---|---|---|
Team A | 3 | -6 |
Team B | 2 | -6 |
Team C | 1 | -6 |
Flexteam | -6 |
Anyone that can help me sort my DAX out?