Pivot Table Issues

Power Pivot DAX Measure for the Percentage of Top Five Football Matches

John WilsonJohn Wilson Nov 12, 2026 868 views

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.
Before you start

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.

Solution 1Recommended

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.

1
Verify the model relationships

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.

2
Create the total-goals measure

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

3
Create the latest-five percentage measure

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.

4
Test the result in a PivotTable

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.

Model structure controls the result: If MatchStats stores one row per goal rather than one row per match, use a distinct match count and a goal-count measure instead of summing home and away goal columns. If the model has no date or sequence field, DAX cannot reliably identify the most recent five matches.
Free Microsoft Office alternative

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. 1. Open the match workbook: Launch WPS Spreadsheets and choose Menu > Open to import the .xlsx or .xls workbook containing the match statistics.
  2. 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. 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.
Use a lightweight spreadsheet workflow to filter the latest five football matches and calculate the percentage of matches with more than two goals.Open and edit Microsoft Excel .xlsx and .xls files with strong format compatibility.Build PivotTables, formulas, filters, and charts in a familiar interface without a complex data-model setup.Migrate existing Excel calculations and reporting layouts with minimal changes.
microsoft office alternative - wps office

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.