Forum Discussion
Solved
Similar formula in Excel's RANK.AVG in Power BI
Hello everyone! Do you know of a similar measure or workaround to get RANK.AVG in Power BI? Thank you!
Hi robibanadera ,
I created a sample pbix file(see attachment), please check whether that is what you want. You can create a measure as below to get the value which is similar with the function Rank.Avg in excel:
Rank.Avg(Ascending) = VAR tab1 = ADDCOLUMNS ( ALLSELECTED('Table'), "NLSales", ROUND ( [Last months Sales] + RAND () / 100.0, 4 ) ) VAR tab2 = ADDCOLUMNS ( tab1, "Rank", RANKX ( tab1, [NLSales],, ASC, DENSE ) ) RETURN AVERAGEX ( FILTER ( tab2, [Last months Sales] = MAX('Table'[Last months Sales]) ), [Rank] )Best Regards
amitchandak
Super User
Super Userrobibanadera , Try like
(Rankx(allselected(Table[Col]), [measure],,desc, dense) +Rankx(allselected(Table[Col]), [measure],,desc, skip)) /2
Can it be a measure? So it would be dynamic when filtering categories. Thank you!