Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
rtaylor
Helper III
Helper III

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

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]
2 ACCEPTED SOLUTIONS
rtaylor
Helper III
Helper III

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)

View solution in original post

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)

View solution in original post

2 REPLIES 2
rtaylor
Helper III
Helper III

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)

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)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.