topic Re: show top 2 or top 3 only in DAX Commands and Tips

show top 2 or top 3 only

reibalboaOMG — Mon, 13 Mar 2023 06:55:16 GMT

hi Everyone

i have a question regarding on TOPN to show only top 2 and top 3, bottom 2, and bottom 3

here is my sample slide the first image link is for top 1, second image is for top 2

and then another page same model but for the top bottom

here's the sample data i've been working on

do i need to make a new measure for this? and new ranking?

my formula is not giving me the right ranking

hope you guys can help me. thank you

Re: show top 2 or top 3 only

Greg_Deckler — Mon, 13 Mar 2023 07:07:38 GMT

reibalboaOMG I would suggest using the Top N filtering in the filter pane. Otherwise, since you are using a measure it appears, wrap your expression (2nd parameter) in CALCULATE. I just made a video on this topic that comes out later this week on Microsoft Hates Greg (Microsoft Hates Greg - YouTube). In the mean time see if this helps, To *Bleep* with RANKX! - Microsoft Power BI Community

Re: show top 2 or top 3 only

reibalboaOMG — Mon, 13 Mar 2023 07:15:29 GMT

hey Greg_Deckler i doing the TopN right now but its not that simple of filtering, like for example: okay i already filtered the top 1 on the first then how can i filtered the top second?

Re: show top 2 or top 3 only

Greg_Deckler — Mon, 13 Mar 2023 07:19:19 GMT

reibalboaOMG OK, then just fix your ranking by tossing in the random CALCULATE function for your expression.

Re: show top 2 or top 3 only

reibalboaOMG — Mon, 13 Mar 2023 07:23:10 GMT

Greg_Deckler can i have a sample formula for this?

thank you very much for the response

Re: show top 2 or top 3 only

Greg_Deckler — Mon, 13 Mar 2023 07:25:31 GMT

reibalboaOMG If you paste your formula first as plain text, I will fix it for you.

Re: show top 2 or top 3 only

reibalboaOMG — Mon, 13 Mar 2023 07:54:19 GMT

Greg_Deckler here's the new measure:

rankingforInfluencers = RANKX(ALL('Template (2)'[KOL Name]),[thisweekcpm],,DESC)

here's the output, i don't know why but i have only 1-4 per influencer in the out put it's a lot

it is suppose to look like this

Re: show top 2 or top 3 only

Greg_Deckler — Mon, 13 Mar 2023 07:59:36 GMT

reibalboaOMG Try:

rankingforInfluencers = RANKX(ALL('Template (2)'[KOL Name]),CALCULATE([thisweekcpm]),,DESC)

Re: show top 2 or top 3 only

reibalboaOMG — Mon, 13 Mar 2023 08:20:22 GMT

Greg_Deckler now it shows only 1 rank

Re: show top 2 or top 3 only

Greg_Deckler — Mon, 13 Mar 2023 08:26:17 GMT

reibalboaOMG Any chance you can post sample data as text as well as the formula for [thisweekcpm] so I can mock this up properly?

Re: show top 2 or top 3 only

reibalboaOMG — Mon, 13 Mar 2023 08:34:24 GMT

Greg_Deckler here's the sample data you might need

and here's my formulas from measure=

rankingforInfluencers = RANKX(ALL('Template (2)'[KOL Name]),CALCULATE('Template (2)'[thisweekcpm]),,DESC)

thisweekcpm = CALCULATE([Postimpcpm] ,ALLSELECTED('Template (2)'),'Template (2)'[weekrank] = MAX('Template (2)'[weekrank]))

weekrank = RANKX(ALL('Template (2)'),'Template (2)'[Week of Update],,ASC,Dense)

Postimpcpm =

1000 * DIVIDE(SUM('Template (2)'[Cost/Post]), SUM('Template (2)'[Post Impression]))

Re: show top 2 or top 3 only

Greg_Deckler — Mon, 13 Mar 2023 08:55:14 GMT

reibalboaOMG I'm not typing all that. Please post your data as text.

Re: show top 2 or top 3 only

reibalboaOMG — Mon, 13 Mar 2023 09:02:23 GMT

Week of Update	Type of KOL	KOL Name	Social Platform	Campaign End Date	Post Reach
1/22/2023	Micro	Influencer 2	IG	1/31/2023	1,029
1/22/2023	Micro	Influencer 3	IG	1/31/2023	1,300
1/22/2023	Macro	Influencer 4	IG	1/31/2023	2,300
1/22/2023	Mega	Influencer 5	IG	1/31/2023	19,021
1/22/2023	Mid-Tier	Influencer 6	IG	1/31/2023	1,000
1/22/2023	Nano	Influencer 7	IG	1/31/2023	920
1/22/2023	Nano	Influencer 8	IG	1/31/2023	982
1/22/2023	Mega	Influencer 9	IG	1/31/2023	2,000
1/22/2023	Macro	Influencer 10	IG	1/31/2023	1,329
1/22/2023	Nano	Influencer 11	FB	1/31/2023	150
1/22/2023	Micro	Influencer 12	FB	1/31/2023	1,000
1/22/2023	Mid-Tier	Influencer 13	FB	1/31/2023	2,043
1/22/2023	Mega	Influencer 14	FB	1/31/2023	25,000
1/22/2023	Mega	Influencer 15	FB	1/31/2023	9,081
1/22/2023	Mid-Tier	Influencer 16	FB	1/31/2023	1,434
1/22/2023	Nano	Influencer 17	FB	1/31/2023	210
1/22/2023	Macro	Influencer 18	FB	1/31/2023	901
1/22/2023	Mid-Tier	Influencer 19	FB	1/31/2023	9,038
1/22/2023	Macro	Influencer 20	FB	1/31/2023	22,894
1/22/2023	Micro	Influencer 21	TikTok	1/31/2023	1,023
1/22/2023	Nano	Influencer 22	TikTok	1/31/2023	150
1/22/2023	Mid-Tier	Influencer 23	TikTok	1/31/2023	45,003
1/22/2023	Macro	Influencer 24	TikTok	1/31/2023	24,002
1/22/2023	Macro	Influencer 25	TikTok	1/31/2023	76,123
1/22/2023	Mega	Influencer 26	TikTok	1/31/2023	102,230
1/22/2023	Mega	Influencer 27	TikTok	1/31/2023	1,231
1/22/2023	Macro	Influencer 28	TikTok	1/31/2023	25,000
1/22/2023	Mid-Tier	Influencer 29	TikTok	1/31/2023	19,201
1/22/2023	Nano	Influencer 30	TikTok	1/31/2023	300
1/22/2023	Mid-Tier	Influencer 31	TW	1/31/2023	2,000
1/22/2023	Mid-Tier	Influencer 32	TW	1/31/2023	521
1/22/2023	Mid-Tier	Influencer 33	TW	1/31/2023	901
1/22/2023	Mega	Influencer 34	TW	1/31/2023	1,923
1/22/2023	Nano	Influencer 35	TW	1/31/2023	220
1/22/2023	Nano	Influencer 36	TW	1/31/2023	712
1/22/2023	Macro	Influencer 37	TW	1/31/2023	1,923
1/22/2023	Mega	Influencer 38	TW	1/31/2023	2,932
1/22/2023	Micro	Influencer 39	TW	1/31/2023	2,500
1/22/2023	Micro	Influencer 40	TW	1/31/2023	800
1/22/2023	Macro	Influencer 41	YT	1/31/2023	18,200
1/22/2023	Nano	Influencer 42	YT	1/31/2023	1,030
1/22/2023	Nano	Influencer 43	YT	1/31/2023	672
1/22/2023	Micro	Influencer 44	YT	1/31/2023	3,500
1/22/2023	Macro	Influencer 45	YT	1/31/2023	9,201
1/22/2023	Micro	Influencer 46	YT	1/31/2023	1,231
1/22/2023	Mega	Influencer 47	YT	1/31/2023	132,022
1/22/2023	Mega	Influencer 48	YT	1/31/2023	12,232
1/22/2023	Mid-Tier	Influencer 49	YT	1/31/2023	4,210
1/22/2023	Mid-Tier	Influencer 50	YT	1/31/2023	10,031
1/29/2023	Mid-Tier	Influencer 1	IG	1/31/2023	1,095
1/29/2023	Micro	Influencer 2	IG	1/31/2023	1,954
1/29/2023	Micro	Influencer 3	IG	1/31/2023	2,009
1/29/2023	Macro	Influencer 4	IG	1/31/2023	2,503
1/29/2023	Mega	Influencer 5	IG	1/31/2023	24,873
1/29/2023	Mid-Tier	Influencer 6	IG	1/31/2023	1,432
1/29/2023	Nano	Influencer 7	IG	1/31/2023	1,021
1/29/2023	Nano	Influencer 8	IG	1/31/2023	1,102
1/29/2023	Mega	Influencer 9	IG	1/31/2023	4,092
1/29/2023	Macro	Influencer 10	IG	1/31/2023	2,531
1/29/2023	Nano	Influencer 11	FB	1/31/2023	203
1/29/2023	Micro	Influencer 12	FB	1/31/2023	1,934
1/29/2023	Mid-Tier	Influencer 16	FB	1/31/2023	9,304
1/29/2023	Nano	Influencer 17	FB	1/31/2023	500
1/29/2023	Macro	Influencer 18	FB	1/31/2023	41,923
1/29/2023	Micro	Influencer 21	TikTok	1/31/2023	1,110
1/29/2023	Nano	Influencer 22	TikTok	1/31/2023	502
1/29/2023	Mid-Tier	Influencer 23	TikTok	1/31/2023	46,921
1/29/2023	Macro	Influencer 24	TikTok	1/31/2023	24,982
1/29/2023	Macro	Influencer 25	TikTok	1/31/2023	76,543
1/29/2023	Mega	Influencer 26	TikTok	1/31/2023	150,983
2/5/2023	Nano	Influencer 11	FB	1/31/2023	250
2/5/2023	Micro	Influencer 12	FB	1/31/2023	2,000
2/5/2023	Mid-Tier	Influencer 16	FB	1/31/2023	9,500

Re: show top 2 or top 3 only

reibalboaOMG — Mon, 13 Mar 2023 09:03:20 GMT

Greg_Deckler sorry about that, try this and it's not the whole data because it has a capacity of a 20,000 characters

Re: show top 2 or top 3 only

Greg_Deckler — Mon, 13 Mar 2023 09:21:07 GMT

reibalboaOMG Pretty sure your weekrank is a column and it needs to be a measure. See attached PBIX file below signature.

weekrank measure = RANKX(ALL('Template (2)'),CALCULATE(MAX('Template (2)'[Week of Update])),,ASC,Dense)

Re: show top 2 or top 3 only

reibalboaOMG — Mon, 13 Mar 2023 09:26:32 GMT

Greg_Deckler

i need that as a column but we can create new measure for weekrank if you need it

Re: show top 2 or top 3 only

Greg_Deckler — Mon, 13 Mar 2023 09:41:47 GMT

reibalboaOMG Pretty sure you need it and you will need to use it in that table visual. Or, you will need to aggregate your weekrank column somehow.

Re: show top 2 or top 3 only

reibalboaOMG — Mon, 13 Mar 2023 09:46:37 GMT

Greg_Deckler i need it to be on the column because i'm using it to get the measurement for the week of update and date of post, so if we need another weekrank it's okay for me to create a new one