Average function to ignore slicer but including FILTERS

[ThomasRichard]

Hi everybody,

I am working with football GPS data. I want to express, for a specific date, the match value of a player compared to his average value on all matches.

I am struggling with my New Measure to calculate the average. Since it has to ignore date slicer, I am using the ALL function. However I still need 3 filters:

Period Name = Match (to get only the data from the matches and not training)

Date = between 2 dates that I can choose (that are different then the selected date on the page slicer)

Total minutes >90

For now I tried to get the first condition right, but these following 2 functions don't work ('Training Raw'[Total Minutes] = the column that I want to average):

Test Measure = AVERAGEX(FILTER(ALL('Training Raw'),'Training Raw'[Period Name]="Match"),'Training Raw'[Total Minutes])

Test Measure = CALCULATE(AVERAGEX(ALL('Training Raw'),'Training Raw'[Total Minutes]),FILTER(ALL('Training Raw'),'Training Raw'[Period Name]="Match"))

I am also thinking to create a whole new table containing match averages, to avoid creating a new measure for each column that I want to average, but I also face problems when trying it (Creating a new summarized table only allows me to do averages of measures, and not of my base data).

Thanks a lot!

Thomas

[ThomasRichard]

@learninPBI5Hard 

https://we.tl/t-pgotUOnxUg

Here is the pbix file. I didn't know one could visualize it without the database files.

[learninPBI5Hard]

Ok, so you want a measure with 3 filters: date, period = "Match", total > 90, and another measure with period = "Match" and total >90 but all date. 

This is to calculate first measure with slicer 'date' on Page

first_measure =  CALCULATE(AVERAGE('Training Raw'[Total Minutes]), FILTER('Training Raw', 'Training Raw'[Total Minutes] > 90 && 'Training Raw'[Period Name] = "Match"))

This is to calculate second measure and ignore slicer 'date' on Page

second_measure=

CALCULATE([first_measure], REMOVEFILTERS('Date'[Date]))

Measure will always include all base data, unless you specifically code to filter the data. Measure will be apart in chart with static column value. Sometimes static column value would be shorten in chart due to filters you choose in dax code and no related data under the filters in dax. 

Please select accept as solution if you have no other questions

[ThomasRichard]

@learninPBI5Hard thank you very much, I will try this solution.

Do you think this is my best way to go, for what I want to achieve? I want to avoid creating a new measure for every column and every situation (page with slicer or not), what I really need is to have the average value for each player to use it for calculation in different reports (percent of average, difference with average, etc), and for different columns (minutes, total distance, sprint distance, etc)

It feels like this should be the easiest way to do it, but when I see the steps, it looks very specific to each page and each of my column, while what I want is a general summarized table.

Do you think I should do it creating a new table that will summarize every column's average (with my filters) for each player?

Thanks a lot,

Kindly

[ThomasRichard]

Thanks for the quick answer, how can I share the pbix file?

I will try your suggestion a bit later when I'll get time.

[learninPBI5Hard]

https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

[learninPBI5Hard]

If you have a pbix file, it would be easier for me to code targeting your variables/columns. You can try 

Calculate(average(A), filter(data, data[column] = "A"), filter(), keepfilters(treatas({}, data[column)), removefilters(data2[column))