Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin 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.
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 🙂
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
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 )
)
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?
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?
@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
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
11 | |
9 | |
7 | |
6 | |
6 |
User | Count |
---|---|
20 | |
11 | |
10 | |
9 | |
7 |