Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
Anonymous
Not applicable

First 3 values of a group

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:

IDEventScoreFirst 3
1A2020
2A2525
3A3030
4A35 
5B2222
6B2626
............

 

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 🙂 

5 REPLIES 5
az38
Community Champion
Community Champion

Hi @Anonymous 

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

 


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn
Mariusz
Community Champion
Community Champion

Hi @Anonymous 

 

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.



Anonymous
Not applicable

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?

Anonymous
Not applicable

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
Community Champion
Community Champion

@Anonymous 

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


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

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.