Power Pivot DAX Measure for the Percentage of Top Five Football Matches
Question details
The user needs a Power Pivot DAX measure that calculates the percentage of football matches with more than two total goals and applies the calculation to the five most recent matches.
- Product
- Microsoft Excel Power Pivot and DAX
- Device & OS
- not provided
- Scenario
- A football match data model contains match statistics, match identifiers, dimensions, and dates. The user wants to evaluate all filtered matches or only the latest five matches while respecting relationships and filters.
- Observed behavior
- The correct measure cannot be confirmed because the relationships between MatchStats, MatchID, DimID, and Dimensions, the goal columns, and the match date ordering are not fully defined.
Confirm that each match has a unique identifier and that the match table contains a reliable date or sequence column. Verify the relationships between the match dimension and MatchStats, and identify the columns used to calculate total goals.
Create a relationship-aware DAX measure for the latest five matches
Use this approach when each match has a date and the data model can filter MatchStats through a match dimension table.
The exact table and column names must be replaced with those in the workbook. The measure below assumes a match dimension named MatchID with MatchID and MatchDate columns, plus a [Total Goals] measure that returns the goals for the current match.
ALLSELECTED preserves report, season, team, and slicer filters while TOPN selects the five most recent matches. The denominator counts the available matches, so seasons or teams with fewer than five matches are handled correctly.
In Excel, open Power Pivot > Manage, choose Diagram View, and confirm that MatchID[MatchID] has a valid relationship to the corresponding MatchStats match key. Also confirm that MatchID[MatchDate] contains the ordering date.
In Power Pivot, select the MatchStats table, open the Calculation Area, and create a measure such as [Total Goals] using the appropriate goal columns, for example: Total Goals := SUM(MatchStats[HomeGoals]) + SUM(MatchStats[AwayGoals]).
In Power Pivot > Calculation Area, create a measure with this pattern: Percentage Latest Five := VAR RecentMatches = TOPN(5, ALLSELECTED(MatchID), MatchID[MatchDate], DESC, MatchID[MatchID], DESC) VAR AvailableMatches = COUNTROWS(RecentMatches) VAR MatchesOverTwo = COUNTROWS(FILTER(RecentMatches, CALCULATE([Total Goals]) > 2)) RETURN DIVIDE(MatchesOverTwo, AvailableMatches). Replace the table and column names as needed.
Choose Insert > PivotTable > From Data Model, place the new measure in Values, and add season, team, or date fields to Rows or Filters. Compare the result with a manually filtered list of the five newest matches.
Calculate the percentage for all filtered matches first
Use this simpler measure when the immediate goal is to validate the goal condition before adding the latest-five filter.
Use WPS Spreadsheets to analyze football match percentages with PivotTables
Power Pivot and DAX are Microsoft Excel features, so WPS Office does not directly execute this model-specific DAX measure. WPS Spreadsheets provides a free, lightweight alternative for importing compatible workbooks, preparing match data, filtering the five newest records, and building PivotTables with familiar spreadsheet tools.
- 1. Open the match workbook: Launch WPS Spreadsheets and choose Menu > Open to import the .xlsx or .xls workbook containing the match statistics.
- 2. Filter the latest five matches: Select the match table, open Data > AutoFilter, sort the match date column from newest to oldest, and retain the five most recent records.
- 3. Calculate and summarize the percentage: Add a helper column that totals the match goals and marks values greater than two, then use Insert > PivotTable or a COUNTIF-based formula to summarize the qualifying percentage.

Frequently Asked Questions
Why can’t the exact Power Pivot measure be provided without the workbook model?
DAX depends on the relationships, table names, match-key columns, goal fields, and date field. Without those definitions, a formula may count rows instead of matches or fail to apply filters correctly.
How should the measure handle a team or season with fewer than five matches?
Use the number of rows returned by TOPN as the denominator rather than always dividing by five. DIVIDE(qualifying matches, available matches) returns the correct percentage when fewer than five matches exist.
What does the latest-five DAX pattern use to identify recent matches?
TOPN sorts the match dimension by a date or sequence column in descending order and limits the result to five rows. Add a unique MatchID as a secondary sort to make ties deterministic.
How can I verify that total goals are being evaluated per match?
Place MatchID[MatchID] and the [Total Goals] measure in a PivotTable. Each match should display its own total; if every row shows the same value, check the relationship between MatchID and MatchStats.
Can WPS Office run Power Pivot DAX measures?
No. Power Pivot and its DAX data-model measures are Microsoft Excel features. WPS Spreadsheets can provide an alternative workflow using compatible Excel files, formulas, filters, and PivotTables.




