Skillfully use the RunningValue function to sort and set rank rankings in groups

Introduction to the outset

There are often sort requirements like this, as well as set the sort ordinal under grouping. For example, in this figure, urban city is required to sort by the Internet sales Amount totals under the province and to identify the rankings under each province.

Implementation process

Do the grouping first, then add a new column rank.

In the group Properties of the city group, its sort is sorted according to SUM aggregation Internet Sales Amount.

Use the RunningValue function in the expression of Rank in the city row.

The function of the following expression is to calculate the number of city after the stateprovince under the specified group, then the first row is 1, and the second line adds up to 2, so that the sort rank NO is achieved.

The use of the RunningValue function can be consulted: https://msdn.microsoft.com/zh-cn/library/dd255229.aspx

Returns a run aggregation of all non-Null numeric values specified by an expression in the given scope. RunningValue (expression, function, scope)

In this example, you can turn Scope into nothing, or country to see how the RunningValue changes under different scopes.

For more bi articles, see the Bi-series essay list (SSIS, SSRS, SSAS, MDX, SQL Server) If you feel this article has helped you, please help recommend it to make it easy for others to see these articles quickly in the Biwork blog recommendations Bar.

Skillfully use the RunningValue function to sort and set rank rankings in groups