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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
IssyB
Frequent Visitor

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 @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

 


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

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
Frequent Visitor

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
Frequent Visitor

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

@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


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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors