Sum Top X Values of data using measure

[rastorey]

Hi

I have a PowerBI dataset consisting of two tables. The table has a field called score that contains several thousand records.

Below is an exampe of my dataset. I am trying to SUM the top X values. For example, unfiltered the data should summing the top 2 values should return 560 (280X2). However using SUMX & Top N formulas it returns 1400, because the top two values are 280 and appear five times in the dataset. 

I would like a solution that can be added as measure so when data are filtered it recalculates accordingly.

My measure would take the top X ranked values (can be defined) and sum only the top 2. It would ignore summing duplicate values unless the same values was within the designated rank. Ie if values ranked 1 and 2 are the same, they would be added together. 

Day	Attempt number	Equipment	Target	Score
Monday	1	A	A4	279
Tuesday	2	B	A4	280
Monday	3	A	A4	279
Tuesday	4	B	A4	279
Tuesday	5	A	A4	280
Wednesday	6	B	A4	280
Monday	7	A	A4	279
Tuesday	8	B	A4	280
Monday	9	A	A4	280

[OwenAuger · Accepted solution]

HI @rastorey 

Interesting problem 🙂

Here are two methods I can think of. I have called the table Data in the code below.

Method 1:

Use the SUBSTITUTEWITHINDEX function to add an index column based on sorting by multiple columns (including Score), then take the rows with the top-ranked index values.

You can make use of a column or columns that can be used to identify a row, and we can use those columns to "break ties" between rows with the same score.

If Attempt number is sufficient to distinguish between rows with the same score, we could write a measure like this. If more columns are needed, extend the code to list them wherever Attempt number is listed.

Sum of Top 2 Scores =
VAR NumItems = 2

-- Select the required columns for sorting
-- These columns must include Score & and other columns that uniquely identify a row
VAR BaseTable =
    SELECTCOLUMNS ( Data, Data[Attempt number], Data[Score] )

-- Duplicate the columns required for sorting using ADDCOLUMNS
VAR ExtendedTable =
    ADDCOLUMNS (
        BaseTable,
        "@Attempt Number", Data[Attempt number],
        "@Score", Data[Score]
    )

-- Create IndexedTable, which will take ExtendedTable and replace
-- the columns also in BaseTable with an Index
-- [@Index] = 0 corresponds to the first or "max" row
VAR IndexedTable =
    SUBSTITUTEWITHINDEX (
        ExtendedTable,
        "@Index", BaseTable,
        Data[Score], DESC, -- Sort Score in descending order
        Data[Attempt number], ASC -- Sort Attempt number ascending for each Score
    )
VAR TopRows =
    TOPN ( NumItems, IndexedTable, [@Index], ASC )
VAR Result =
    SUMX ( TOPN ( NumItems, IndexedTable, [@Index], ASC ), [@Score] )
RETURN
    Result

The variable IndexedTable looks like this:

@Attempt Number	@Score	@Index
2	280	0
5	280	1
6	280	2
8	280	3
9	280	4
1	279	5
3	279	6
4	279	7
7	279	8

Method 2:

This method takes the top N values of Score, counts the occurrences of each cumulatively, then weights each Score by the required count so as not to exceed the number of items to be included.

Sum of Top 2 Scores =
VAR NumItems = 2
VAR TopNScore =
    TOPN ( NumItems, VALUES ( Data[Score] ) )
VAR Result =
    SUMX (
        TopNSCore,
        VAR CurrentScore = Data[Score]
        VAR CurrentCount =
            CALCULATE ( COUNTROWS ( Data ) )
        VAR CumulativeCount =
            CALCULATE ( COUNTROWS ( Data ), Data[Score] >= CurrentScore )
        VAR CumulativeCountPre = 
            CumulativeCount - CurrentCount
        VAR Weighting =
            MAX (
                BLANK (), -- Could also use 0
                -- Count of current Score to include
                MIN (
                    NumItems - CumulativeCountPre,
                    CumulativeCount - CumulativeCountPre
                ) 
            )
        RETURN
            Weighting * Data[Score]
    )
RETURN
    Result

With your sample data, the top 2 Scores are 280 & 279, and the values calculated in the measure look like this:

Score	Count	Cumulative Count	Weighting
280	5	5	2
279	4	9	0

Regards,

Owen