topic how to calculate the frequency of Top N of each id in each week in DAX Commands and Tips

how to calculate the frequency of Top N of each id in each week

oakfootballclub — Wed, 03 Jul 2024 08:31:11 GMT

week	id	value
w1	E01	163
w1	E01	145
w1	E02	94
w1	E02	35
w1	E03	123
w1	E04	8
w1	E06	177
w1	E07	38
w2	E01	103
w2	E02	180
w2	E03	31
w2	E04	57
w2	E06	88
w2	E07	179
w2	E08	69
w3	E02	177
w3	E03	176
w3	E04	62
w3	E06	92
w3	E07	163
w4	E01	160
w4	E02	73
w4	E03	38
w4	E04	47
w4	E06	64
w4	E07	152
w4	E08	129

based on the data above,

notice that the in each week may exist same id with different values,

I want to calculate the frequency of Top 3 value of each week of each id in each, for example,

in week w1, Top 3 is: E01,E02,E06
in week w1, Top 3 is:E02,E07,E01
in week w1, Top 3 is:E02,E03,E07
in week w1, Top 3 is:E01,E07,E08

so week top3 frequency of E01 is 3,

that of E02 is 1,

that of E03 is 2,

that of E04 is 0,

........

do you know how to caluculate it in DAX with high performance? Thank you.

Re: how to calculate the frequency of Top N of each id in each week

aduguid — Wed, 03 Jul 2024 14:21:48 GMT

Try using this calculated column

RankWithinWeek = RANKX( FILTER( TableName, TableName[week] = EARLIER(TableName[week]) ), TableName[value], , DESC, DENSE )

Then you can use this measure

Top3Frequency = VAR CurrentID = SELECTEDVALUE(TableName[id]) RETURN CALCULATE( COUNTROWS( FILTER( TableName, TableName[RankWithinWeek] <= 3 && TableName[id] = CurrentID ) ) )

Re: how to calculate the frequency of Top N of each id in each week

Anonymous — Mon, 15 Jul 2024 02:12:54 GMT

Hi,aduguid

thanks for your concern about this issue.
Your answer is excellent!

And I would like to share some additional solutions below.

Hi,oakfootballclub

I am glad to help you.

According to your description, you want to distinct count by max date?

If I understand you correctly, then you can refer to my solution.

New table, calculate the value grouped according to week and id.

Table2 = ADDCOLUMNS ( SUMMARIZE ( Table_, Table_[week], Table_[id], "TotalValue", SUM ( Table_[value] ) ), "TotalValueByWeekAndID", [TotalValue] )

2.New Column, according to the value calculated in the first step of the descending order, to get the top three values.

RankWithinWeekAndID = RANKX ( FILTER ( ALL ( 'Table2' ), 'Table2'[week] = EARLIER ( 'Table2'[week] ) ), 'Table2'[TotalValueByWeekAndID], , DESC, DENSE )

3.New Measure, and then use the Measure to filter the visual.

Top3Frequency = VAR CurrentID = SELECTEDVALUE ( Table2[id] ) RETURN CALCULATE ( COUNTROWS ( FILTER ( Table2, Table2[RankWithinWeekAndID] <= 3 && Table2[id] = CurrentID ) ) )

4.Here is the result:

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,
Fen Ling,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.