Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I am trying to learn to work with CALCULATE to modify filter contexts in my reports but really struggling. I have created this simple example to see if I can get some help with this basic question.
My data is simply as follows... 6 players on 3 different teams scoring goals.
Player | Team | Game | Goals |
A | NY | 1 | 2 |
A | NY | 2 | 4 |
B | FLA | 1 | 1 |
B | FLA | 2 | 3 |
B | FLA | 3 | 1 |
C | DET | 1 | 1 |
C | DET | 2 | 2 |
D | NY | 1 | 1 |
D | NY | 2 | 2 |
E | FLA | 1 | 3 |
E | FLA | 2 | 1 |
E | FLA | 3 | 1 |
F | DET | 2 | 1 |
F | DET | 3 | 2 |
If I want to sum Player A's goals across all games, it is simply,
PlayerGoals = SUM('Table'[Goals])
This correctly returns 6.
If I want to sum all of Team NY's goals, it is simply,
TeamGoals = CALCULATE(SUM('Table'[Goals]), FILTER('Table','Table'[Team]="NY"))
This correctly returns 9 (i.e. the sum of player A's goals and player D's goals!).
So then, why is the share of goals for player A not returning correctly when I try,
%Share_of_Team_Goals = DIVIDE(
SUM('Table'[Goals]),
CALCULATE(SUM('Table'[Goals]), FILTER('Table','Table'[Team]="NY")
)
)
*If I have a slicer selecting player "A", this returns 100%, because 6 goals is 100% of player A's goals. If I remove the slicer, it returns 278%, because the goals for all players are 2.78x the goals for NY players.
What I want to return is 66% for player A, but the measure should work dynamically regardless of which player I select.
Anyone have any tips for me? I've been tinkering with all sorts of ALL() and KEEPFILTERS() and REMOVEFILTERS() and I'm really just stuck in a loop. Please help!
Solved! Go to Solution.
Expertise = List.Accumulate( {Days as from Today}, {Skills and Knowledge}, (Current, Everyday) => Current & Day.LeanAndPractise(Everyday) ) |
Expertise = List.Accumulate( {Days as from Today}, {Skills and Knowledge}, (Current, Everyday) => Current & Day.LeanAndPractise(Everyday) ) |
I'm speechless; Thank you! The VALUES() function is something I did not come across in my research but it makes so much sense (hindsight being 20/20) because DAX needs to know the "value" of the team that the player is on. Eureka!
Hi,
Your Output part is confusing to me, can you elaborate a bit the output part or if you can showcase the output values in excel, it will works
Are you looking for below result.
This is it yes, but it needs to work for any player/ team! Above solution only works for NY. I think that @ThxAlot solved it in his post! Thanks for your response and support!