Forum Discussion
Measure to calculate opposite value?
My simplified data looks like this:
The first table ("Player List") is connected to the second table ("Player Stats") by Player Name. I have a simple measure that sums stats in table "Player Stats".
So in my table visual, I could show Team from the "Player List" table with my measure and get NYY = 10 and WSH = 2.
How would I be able to flip this measure around so that when NYY is displayed on the table visual it sums the measure based on that team's opponent (WSH) rather than the team listed in the table. So the result would be flipped for the proposed measure. NYY would = 2 and WSH would = 10.
My attempted measure is this, but it returns blank:
Opp Measure = VAR Opp = MAX('Player List'[Opponent]) RETURN CALCULATE([Measure],ALL('Player List'),FILTER('Player List','Player List'[Team]= Opp))
Anonymous
Maybe provide a visual explain of your expected output, I am a bit unsure about your demand. For the measure, try:
Opp Measure = CALCULATE([Measure],FILTER(ALL('Player List'),'Player List'[Team]= MAX('Player List'[Opponent])))Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
- Greg_Deckler
Community Champion
Anonymous - Here is one way:
Measure 2 = VAR __Opp = MAX('Player List'[Opponent]) VAR __Opponents = SELECTCOLUMNS(FILTER(ALL('Player List'),[Opponent] = __Opp),"Player",[Player]) RETURN SUMX(FILTER(ALL('Player Stats'),NOT([Player] IN __Opponents)),[Stats])Greg_Deckler Thanks for the reply. I simplified my data in the example. I think I understand your formula, but I think it is producing a higher than expected value, because there are more than just two teams in the dataset.
I think your formula is saying to sum up everything else if it's not the other. Which in my dataset is a lot more teams since there are more than 2. I could be wrong, but I think that's it.
- Greg_Deckler
Anonymous - Well, it is grabbing the MAX opponent in context. I don't know what context that might be or how you want it to work if there are multiple opponents. What do you want to do if there are multiple opponents in context?
Anonymous
Maybe provide a visual explain of your expected output, I am a bit unsure about your demand. For the measure, try:
Opp Measure = CALCULATE([Measure],FILTER(ALL('Player List'),'Player List'[Team]= MAX('Player List'[Opponent])))Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
