Display the Category for the Top Numer or Number 1 Rank.

[rtaylor]

Hello,

I have a TOPN Expression to pull the top row of a table based on the variance expression. I am wondering is there a way to display the BudgetMap[Budget Summary Line] Column for this value.

Var rankotherrentvar = TOPN(1,filter(SUMMARIZE(BudgetMap,BudgetMap[Budget Summary Ovrhead],BudgetMap[Budget Summary Line],"Actuals",actual,"Pyactuals",pyactual,"actvar",actvariancepy1a,"actincrease",actpercentincrease,"Budget",budget,"actvbud",actvbud,"py1actvbud",actpy1vbud),BudgetMap[Budget Summary Ovrhead] = "Other Rent"),[actvar],DESC)

My end result would be a text expression:

"The highest variance in Other Rent, comes from" & BudgetMap[Budget Summary Line]  & "at" & [actvar]

[rtaylor · Accepted solution]

I figure it out. You need to use firstnonblank to pull the column name.

Var rankxfix2 = rankx(Filter(SUMMARIZE(BudgetMap,BudgetMap[Budget Summary Ovrhead],BudgetMap[Budget Summary Line],"actvar",actvariancepy1a),BudgetMap[Budget Summary Ovrhead] = "Other Rents"),actvariancepy1a,,DESC)

Var firstnonblanktest = FIRSTNONBLANK(BudgetMap[Budget Summary Line],rankxfix2=1)

[rtaylor · Accepted solution]

I figure it out. You need to use firstnonblank to pull the column name.

Var rankxfix2 = rankx(Filter(SUMMARIZE(BudgetMap,BudgetMap[Budget Summary Ovrhead],BudgetMap[Budget Summary Line],"actvar",actvariancepy1a),BudgetMap[Budget Summary Ovrhead] = "Other Rents"),actvariancepy1a,,DESC)

Var firstnonblanktest = FIRSTNONBLANK(BudgetMap[Budget Summary Line],rankxfix2=1)

[rtaylor · Accepted solution]

I wanted to edit this and make is more clear.

1st create a measure for the table with the rank you wish to use

Measure 1 =

var tablerank = Filter(Filter(Filter(all('Actual Details All'[SupplyDetailCat1]),'Actual Details All'[SupplyDetailCat1]<>BLANK()),[Act_Detail_SUB_Act_Detail_PY1]<>0),[Act_Detail_SUB_Act_Detail_PY1]<>blank())

return

if([Act_Detail_SUB_Act_Detail_PY1]=0||[Act_Detail_SUB_Act_Detail_PY1]=blank(),blank(),if(calculate(FIRSTNONBLANK('Actual Details All'[SupplyDetailCat1],'Actual Details All'[SupplyDetailCat1]))=BLANK(),blank(),calculate(rankx(tablerank,[Act_Detail_SUB_Act_Detail_PY1],,DESC,Dense))))

2nd Use Firstnonblank to pull the desired label

ToplineitemSuppliesServices = FirstNONBLANK(Filter(values('Actual Details All'[SupplyDetailCat1]),[SuppliesServicesLineRankExp]=1&&'Actual Details All'[SupplyDetailCat1]<>blank()),1)