Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Blue246
Frequent Visitor

How To Create A Measure That is Group And Ignores Filters

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:

PlayerAverage Moves Per DayTeam
Ted7Red
Bill2Blue
Mark23Green
Luke3Red


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:

 

PlayerAverage Moves Per DayTeamAvg Moves By Team Per Day
Ted7Red5
Bill2Blue2
Mark23Green23
Luke3Red5



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.

8 REPLIES 8
Blue246
Frequent Visitor

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.

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

lbendlin
Super User
Super User

in your measure use REMOVEFILTERS(<player>)

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

Blue246_0-1710433583631.png

 

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...

PlayerMovesTeamDate
TimLeftRed1/1/2001
TimRightRed1/1/2001
TimLeftRed1/1/2001
TimDownRed1/1/2001
MarkUpBlue1/1/2001
MarkRightBlue1/1/2001
MarkUpBlue1/1/2001
MarkUpBlue1/1/2001
JoeLeftBlue1/1/2001
JoeLeftBlue1/1/2001
JoeRightBlue1/1/2001
JoeRightBlue1/1/2001
JoeRightBlue1/1/2001
JoeRightBlue1/1/2001
BobDownRed1/1/2001
BobUpRed1/1/2001
BobUpRed1/1/2001
BobRightRed1/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: 


PlayerAvg Moves/DayTeamAvg Team Moves/Day
Tim4Red
Mark4Blue5
Joe6Blue5
Bob4Red4

Your sample data only includes a single day,

 

lbendlin_0-1710717996776.png

 

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.