First 3 values of a group

[IssyB]

First post so apologies if I don't include enough info - I have also been using power BI only for a few weeks so please go easy on me!

I'm trying to get the first 3 values of a group but not sure if this is possible with Power BI

Here's an illustration of what I'm trying to achieve:

ID	Event	Score	First 3
1	A	20	20
2	A	25	25
3	A	30	30
4	A	35
5	B	22	22
6	B	26	26
...	...	...	...

After this, I do not need the null "First 3" values.

I'm thinking a new table with summarized columns would be great for what I'm trying to do as I need to then perform calculations ([sum of the first 3 per group]/[highest possible score]) on these values.

Thanks 🙂 

[az38]

Hi @IssyB 

if the first 3 row is the first 3 ID of Event you could use the next technique:

1. create a rank calculated column

ColumnRank = RANKX(FILTER('Table1';'Table1'[Event]=EARLIER('Table1'[Event]));'Table1'[ID];;ASC)

2. create a measure

Measure = 
var sumFirst3 = CALCULATE(SUM(Table1[Score]);ALLEXCEPT(Table1;Table1[Event]);Table1[ColumnRank]<4)
var sumCat = CALCULATE(MAX(Table1[Score]);ALLEXCEPT(Table1;Table1[Event]))
return
divide(sumFirst3;sumCat)

do not hesitate to give a kudo to useful posts and mark solutions as solution

LinkedIn

[Mariusz]

Hi @IssyB 

You can try this as an alternative, no need for calculated column and you could calculate Score with SUMX instead.

Measure = 
VAR __tbl = CALCULATETABLE( 'Table', ALL( 'Table' ), VALUES( 'Table'[Event] ) )
VAR __top = TOPN( 3, __tbl, 'Table'[ID], ASC )
RETURN 
DIVIDE(
    CALCULATE( SUM( 'Table'[Score] ), __top ),
    CALCULATE( MAX( 'Table'[Score] ), __tbl )
)

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

[IssyB]

Hi @Mariusz!

After a few days of occasionally trying to fix this I've finally almost got where I need to be ... just one problem that you may be able to help with: it's not summing all the values!

For example, for one event the top scores are 5, 1, 5 yet it is summing this up to 6 as opposed to 11, any ideas?

[IssyB]

Cheers @az38!

Unfortunately, my dataset is a tad more complex than my illustration. In the real data, the ID is tied to an attempt at getting a score, so looks more like:

(Attempt) ID

8734

9946

5485

9503

...

This, in turn, has a date stamp. The "score" is actually a measure using the attempt date.

Would it be possible to use DATEDIFF to get the difference in days from attempt to today, then use RANK to filter the first 3 days?

[az38]

@IssyB 

if you will use DATEDIFF inside a calculated column, it should work. just replace ID to your new column name - ranked key

do not hesitate to give a kudo to useful posts and mark solutions as solution