Forum Discussion

kaye123's avatar
kaye123
Frequent Visitor
3 years ago

TopN Based on Characters

I am trying to get only the last 4 quarters or last 13 weeks  that will be using for the Sparkline reference. My only reference of ranking is based on sorting it form highest to lowest. But since thi...
PowerQuery
TopN
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
3 years ago

kaye123 , Usually if number are sortable we can create rank and use that, In a separate table is best

 

example

new column in a separate week table

Week Rank RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format

 

measure example

 

Last 8 weeks = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-8 && 'Date'[Week Rank]<=max('Date'[Week Rank])))

 

Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

 

 

kaye123's avatar
kaye123
Frequent Visitor
3 years ago

Thanks for this. I was able to create a table with showing specific rankings thanks for that. Now, I need this table to be flexible, in which ranking will depend on what i chose in the slicers or page filters. 

I created a measure like this below. but getting error on the scalar value issue. 

Top4Quarters =
Var tbl = SUMMARIZE(FiscalCalendar,FiscalCalendar[FiscalQuarter])
Var RNK_tbl = ADDCOLUMNS(tbl,"Rank",RANKX(tbl,[FiscalQuarter],[FiscalQuarter],DESC,Dense))
Return
    CALCULATE(RNK_tbl, FILTER(RNK_tbl,[Rank]>5))
 

 

I plan to use this as my x-axis on the sparklines. only showing last 4 quarters or last 13 weeks. depending on what year i choose from the slicer.