Excel Dax 简明教程

Excel DAX - Ranking and Comparing Values

如果你只想显示一列或数据透视表中前 n 个项目，则有以下两个选项 −

If you want to show only the top n number of items in a column or PivotTable, you have the following two options −

  1. You can select n number of top values in the PivotTable.

  2. You can create a DAX formula that dynamically ranks values and then uses the ranking values in a Slicer.

Applying a Filter to Show only the Top Few Items

若要选择 n 个排名前的值在数据透视表中显示，请执行以下操作 −

To select n number of top values for display in the PivotTable, do the following −

  1. Click the down arrow in the row labels heading in the PivotTable.

  2. Click the Value Filters in the dropdown list and then click Top 10.

applying a filter

出现“前 10 筛选器 (<column name>)”对话框。

Top 10 Filter (<column name>) dialog box appears.

  1. Under Show, select the following in the boxes from left to right. Top18 (The number of top values that you want to display. The default is 10.)Items.In the by box, select Medal Count.

medal count

  1. Click OK. The top 18 values will be displayed in the PivotTable.

Advantages and Disadvantages of Applying Filter

Advantages

  1. It is simple and easy to use.

  2. Suitable for tables with large number of rows.

Disadvantages

  1. The filter is solely for display purposes.

  2. If the data underlying the PivotTable changes, you must manually refresh the PivotTable to see the changes.

Creating a DAX Formula That Dynamically Ranks Values

可以使用包含已排序值的 DAX 公式创建计算列。然后，可以在 resulting 计算列上使用切片器来选择要显示的值。

You can create a calculated column using a DAX formula that contains the ranked values. You can then use a slicer on the resulting calculated column to select the values to be displayed.

可以根据行中某个给定值的较大值通过计算同个表格中值大于该值的行数来获得排序值。此方法返回以下内容

You can obtain a rank value for a given value in a row by counting the number of rows in the same table having a value larger than the one that is being compared. This method returns the following −

  1. A zero value for the highest value in the table.

  2. Equal values will have the same rank value. If n number of values are equal, the next value after the equal values will have a nonconsecutive rank value adding up the number n.

例如，如果你有一个带有销售数据的“Sales”表格，你可以创建一个计算列，按照销售金额值的排序如下创建

For example, if you have a table ‘Sales’ with sales data, you can create a calculated column with the ranks of the Sales Amount values as follows −

= COUNTROWS (FILTER (Sales,
   EARLIER (Sales [Sales Amount]) < Sales [Sales Amount])
) + 1

接下来，你可以在新的计算列上插入切片器并按排序选择性地显示值。

Next, you can insert a Slicer on the new calculated column and selectively display the values by ranks.

Advantages and Disadvantages of Dynamic Ranks

Advantages

  1. The ranking is done in the table and not on a PivotTable. Hence, can be used in any number of PivotTables.

  2. DAX formulas are calculated dynamically. Hence, you can always be sure that the ranking is correct even if the underlying data has changed.

  3. Since the DAX formula is used in a calculated column, you can use the ranking in a Slicer.

  4. Suitable for tables with large number of rows.

Disadvantages

因为 DAX 计算对于计算来说过于昂贵，所以此方法可能不适合行数较多的表格。

Since the DAX calculations are computationally expensive, this method might not be suitable for tables with large number of rows.