0 votes
1 view
in BI by (17.6k points)

I have a simple data set showing the number of points scored by a player. It is plotted across a [Week Number] x-axis. I added a Running Sum calculation, as a Line, split by Player (color).

Running Sums by Week

For each week, I would like to identify the top player, by total points achieved to-date.
For example, in Week 3, the top player was GREEN with a running total of 8 points.

I tried creating a LOD function {FIXED [WEEK]: MAX(RunningSum)} but it complains that I cannot aggregate an aggregate.

I tried WINDOW_MAX(), RANK(), and tried adjusting the Table Calc, Advanced, Partitioning and Addressing feature.

Is there no way to identify the MAX of a collection of running totals, by discrete [Week Number]?

This can be done fairly easily in TSQL by wrapping a query in outer queries and applying Window functions with partitions defined.

1 Answer

0 votes
by (47.2k points)
  • You can make use of RANK(), you just need to make sure the table calculation is being computed using Player. I imagine your [RunningSum] field looks something like this:

RUNNING_SUM(SUM([Points]))

  • Create a table calculation (let's call it [Player Rank]):

RANK([RunningSum])

  • Then we need to edit the table calculation. You can do this directly from the Calculated Field edit window (there's a blue link above the OK and Apply buttons that says something like "Default Table Calculation").

  • We want to compute the rank of our players, so we'll go into "Compute Using:" and select Player.

  • For a quick sanity check, drag [Player Rank] into Tooltip and check it out.

Related questions

Welcome to Intellipaat Community. Get your technical queries answered by top developers !


Categories

...