Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 @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
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 )
)
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?
User | Count |
---|---|
47 | |
28 | |
23 | |
18 | |
15 |
User | Count |
---|---|
55 | |
34 | |
18 | |
17 | |
15 |