shellp
Board Regular
- Joined
- Jul 7, 2010
- Messages
- 201
- Office Version
- 365
- 2021
- 2010
- 2007
- Platform
- Windows
Hello,
Below is the code that works(and a resulting picture) but it shows all that match the criteria. How can I edit the code so that it only displays the top 5 groups by volumes of cases?
Below is the code that works(and a resulting picture) but it shows all that match the criteria. How can I edit the code so that it only displays the top 5 groups by volumes of cases?
Excel Formula:
=LET(CC,(UNIQUE(FILTER(raw[HIG_Short_Desc],(raw[MRP]=Report_Doc)*(raw[Fyear]=Sel_Year)))),
a,COUNTIFS(raw[MRP],Report_Doc,raw[Fyear],"="&Sel_Year,raw[HIG_Short_Desc],CC),
b,SUMIFS(raw[totaldays],raw[MRP],Report_Doc,raw[HIG_Short_Desc],CC,raw[Fyear],"="&Sel_Year),
c,SUMIFS(raw[acutedays],raw[MRP],Report_Doc,raw[HIG_Short_Desc],CC,raw[Fyear],"="&Sel_Year),
d,COUNTIFS(raw[MRP],Report_Doc,raw[HIG_Short_Desc],CC,raw[Fyear],"="&Sel_Year,raw[Grp2],"=Typical"),
e,SUMIFS(raw[acutedays],raw[MRP],Report_Doc,raw[HIG_Short_Desc],CC,raw[Fyear],"="&Sel_Year,raw[Grp2],"=Typical"),
f,SUMIFS(raw[HIG_ELOS_R],raw[MRP],Report_Doc,raw[HIG_Short_Desc],CC,raw[Fyear],"="&Sel_Year,raw[Grp2],"=Typical"),
g,COUNTIFS(raw[MRP],Report_Doc,raw[HIG_Short_Desc],CC,raw[Fyear],"="&Sel_Year,raw[Grp2],"=Long Stay"),
h,SUMIFS(raw[acutedays],raw[MRP],Report_Doc,raw[HIG_Short_Desc],CC,raw[Fyear],"="&Sel_Year,raw[Grp2],"=Long Stay"),
i,SUMIFS(raw[HIG_ELOS_R],raw[MRP],Report_Doc,raw[HIG_Short_Desc],CC,raw[Fyear],"="&Sel_Year,raw[Grp2],"=Long Stay"),
SORT(CHOOSE({1,2,3,4,5,6,7,8,9,10,11,12,13,14},CC,a,ROUND(b/a,1),ROUND(c/a,1),COUNTIFS(raw[MRP],Report_Doc,raw[HIG_Short_Desc],CC,raw[Fyear],"="&Sel_Year,raw[Grp2],"=Typical"),ROUND(d*100/a,1),IFERROR(ROUND(e/d,1),0),IFERROR(ROUND(f/d,1),0),IFERROR((ROUND(e/d,1))/(ROUND(f/d,1)),1),g,ROUND(g*100/a,1),IFERROR(ROUND(h/g,1),0),IFERROR(ROUND(i/g,1),0),IFERROR((ROUND(h/g,1))/(ROUND(i/g,1)),0),1),2,-1))