Sum Values with multiple filters and a "condition"

[Fabio74]

Hi everyone, I hope my question doesn't already have an aswer. I've tried to search in other posts but, since I'm new to PowerBi, I'm not very good at adapting possible solutions for cases similar to mine.  If that's the case, I apologize in advance.

I was trying to come up with a formula (possibly a measure but I'm starting with a column because it's easier for me to evaluate the results) to calculate the contribution of points (in %) for each player to the total of points of each team. 

Here's my sample table:

Day Nr.	Team	PlayerName	Vote	Played	Tot Points * Team * Day	% * Player * Team
Day 1	Team A	Player 1	6	Yes	66	6,57%
Day 2	Team A	Player 1	6	Yes	68	6,57%
Day 3	Team A	Player 1	5,5	Yes	71	6,57%
Day 4	Team A	Player 1	0	No	55	6,57%
Day 5	Team A	Player 1	0	No	88	6,57%
Day 6	Team A	Player 1	9,5	Yes	72	6,57%
Day 7	Team A	Player 1	10	Yes	77	6,57%
Day 8	Team A	Player 1	11	No	66	6,57%
Day 9	Team B	Player 1	0	No	59	0,00%
Day 10	Team B	Player 1	0	No	63	0,00%
Day 11	Team B	Player 1	0	No	87	0,00%
Day 12	Team B	Player 1	0	No	72	0,00%
Day 13	Team B	Player 1	0	No	65	0,00%
Day 14	Team B	Player 1	0	No	68	0,00%
Day 15	Team B	Player 1	0	No	82	0,00%
Day 16	Team B	Player 1	0	No	70	0,00%

As I want to consider the vote of the player only when he actually contributed to the total score of the team (when he played), I need to filter by name of the player, by team and when "Played" is "yes".

In Excel (the result is in the last column of the above table) it would be something as simple as this 

=SUMIFS([Vote];[Team];[@Team];[Played];"Yes";[PlayerName];[@PlayerName])/SUMIFS([Tot Points * Team * Day];[Team];[@Team])

but in DAX I'm struggling with the played/not played part.
I came up first with the following formula 

% P * Player = CALCULATE(SUM(Table1[Vote]), ALLEXCEPT(Table1, Table1[Player], Table1[Team])))/ CALCULATE(SUM(Table1[Tot Points * Team * Day]), ALLEXCEPT(Table1, Table1[Team]))

but since it doesn't take into account the filter on the "Played" field, the result is not what I'd like (for Team A takes into account the vote of Player 1 for Day 8 when he was not put in the line-up by the Manager).
I've tried to add an additional filter to the previous formula

% P * Player = CALCULATE(SUM(Table1[Vote]), FILTER(Table1, Table1[Played] = "Yes"), ALLEXCEPT(Table1, Table1[Player], Table1[Team])))/ CALCULATE(SUM(Table1[Tot Points * Team * Day]), ALLEXCEPT(Table1, Table1[Team]))

but the result is even worse (much worse - complete "nonsense" in respect of the desiderd outcome).

I think I'm not very far from obtaining the desidered result, but the syntax (or my faulty logic possibly) is killing me ... XD

Can somebody please point me in the right direction?

Thanks in advance.

[AlexisOlson · Accepted solution]

You don't look too far off but one thing you may not realize is that where you have FILTER(Table1, Table1[Played] = "Yes"), the table, Table1, is evaluated within the local filter context rather than representing the entire table. The FILTER function adds additional filters but Table1 is already filtered by Day, Team, Player, Played etc. and you are adding another filter. What you actually intend is to apply this condition to the less filtered version of the table:

FILTER ( ALLEXCEPT ( Table1, Table1[Player], Table1[Team] ), Table1[Played] = "Yes" )

This is a bit ugly, so I'd suggest this instead:

% P * Player =
DIVIDE (
    CALCULATE (
        SUM ( Table1[Vote] ),
        Table1[Played] = "Yes",
        ALLEXCEPT ( Table1, Table1[Player], Table1[Team] )
    ),
    CALCULATE (
        SUM ( Table1[Tot Points * Team * Day] ),
        ALLEXCEPT ( Table1, Table1[Team] )
    )
)

Note that the boolean condition Table1[Played] = "Yes" is equivalent to FILTER ( ALL ( Table1[Played] ), Table1[Played] = "Yes" ) but easier to read.

[Fabio74]

Thank you very much @AlexisOlson , also for taking the extra time to explain, which is much appreciated. You are absolutely right about the clarity of your second formula. Not really knowing what I'm doing I have a tendency to "add layers" instead, trying to replicate Excel 😄