How To Create A Measure That is Group And Ignores Filters

[Blue246]

Hi!

So basically i need to create a measure that is grouped and does not get affected a row filter.

Lets say i have a table:

Player	Average Moves Per Day	Team
Ted	7	Red
Bill	2	Blue
Mark	23	Green
Luke	3	Red

Average Moves Per Day is a measure counting rows by person by date.
I need to create a similar measure for Team that shows the average moves per day for the whole team and is not filtered down by Player see below, the output should look like:

Player	Average Moves Per Day	Team	Avg Moves By Team Per Day
Ted	7	Red	5
Bill	2	Blue	2
Mark	23	Green	23
Luke	3	Red	5

I want to see the Average Moves By Team in each row for someone on that team. Since the Red team has 10 total moves divided by two people in the team, each row with a "Red" team member should have the value 5 for the measure etc.

[Blue246]

Unfortunately this does not work as when i need to filter by Team, it then shows all Players and not only the players related to the Team filtered.

[lbendlin]

 It will only include the players on the same team as the player in your current context.

[lbendlin]

in your measure use REMOVEFILTERS(<player>)

[Blue246]

This did not work, it just gives an un-rounded version of the average moves column.

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[Blue246]

Player	Moves	Team	Date
Tim	Left	Red	1/1/2001
Tim	Right	Red	1/1/2001
Tim	Left	Red	1/1/2001
Tim	Down	Red	1/1/2001
Mark	Up	Blue	1/1/2001
Mark	Right	Blue	1/1/2001
Mark	Up	Blue	1/1/2001
Mark	Up	Blue	1/1/2001
Joe	Left	Blue	1/1/2001
Joe	Left	Blue	1/1/2001
Joe	Right	Blue	1/1/2001
Joe	Right	Blue	1/1/2001
Joe	Right	Blue	1/1/2001
Joe	Right	Blue	1/1/2001
Bob	Down	Red	1/1/2001
Bob	Up	Red	1/1/2001
Bob	Up	Red	1/1/2001
Bob	Right	Red	1/1/2001

The "Avg Moves Per Day" column comes from a measure:

CALCULATE(

    AVERAGEX(VALUES(Date, Moves),

    FILTER(Moves>0))

The Moves measure used in the above measure is a count of moves. So the ends result gives me the average moves by player per day when putting player and that measure in a table.

I am trying to do something similar for Team where it gives me Average Moves Per Team like so: 

Player	Avg Moves/Day	Team	Avg Team Moves/Day
Tim	4	Red	4
Mark	4	Blue	5
Joe	6	Blue	5
Bob	4	Red	4

[lbendlin]

Your sample data only includes a single day,

[Blue246]

I am having issues making this work because "Moves" is a measure and it won't allow me to pull it into SUMMARIZECOLUMNS. That measure already gets the average moves per day as a whole, i do not need to filter by individual date