i was able to make good progress with y'alls input to refine this calc, but i am still beating my head against the wall with this calculation involving rank(total aggr( ; it keeps referencing the visible results on straight table. . . any help would be greatly appreciated. as a note, the calcs in the middle work as expected. probably staring at this too long.
//rank(total aggr(
if(
if(
aggr(nodistinct rank(sum({1<Market={'$(possibleMarkets)'},[MEGA CATEGORY]={'$(possibleMegaCategory)'}>}aggr(nodistinct sum({1<Market={'$(possibleMarkets)'},[MEGA CATEGORY]={'$(possibleMegaCategory)'}>}$(metric)),Market,[MEGA CATEGORY],CATEGORY,SUBCATEGORY,[PRICE TIER],SIZE,key))),Market,[MEGA CATEGORY],CATEGORY,[PRICE TIER],key)
<=
sum(aggr(nodistinct aggr(nodistinct sum($(metric)),Market,[MEGA CATEGORY],CATEGORY,[PRICE TIER])/aggr(nodistinct sum({1}$(metric)),Market,[MEGA CATEGORY])*$(SKUs),Market,[MEGA CATEGORY],CATEGORY,[PRICE TIER])),1,'')
<>1,
sum({1<Market={'$(possibleMarkets)'},[MEGA CATEGORY]={'$(possibleMegaCategory)'}>}$(metric))
*
aggr(rangesum(above(sum({1<Market={'$(possibleMarkets)'},[MEGA CATEGORY]={'$(possibleMegaCategory)'}>}$(metric)),0,rowno())),Market,[MEGA CATEGORY],CATEGORY, SUBCATEGORY,[PRICE TIER],SIZE, key)
/
aggr(nodistinct rank(sum({1<Market={'$(possibleMarkets)'},[MEGA CATEGORY]={'$(possibleMegaCategory)'}>}aggr(nodistinct sum({1<Market={'$(possibleMarkets)'},[MEGA CATEGORY]={'$(possibleMegaCategory)'}>}$(metric)),Market,[MEGA CATEGORY],CATEGORY,SUBCATEGORY,[PRICE TIER],SIZE,key))),Market,[MEGA CATEGORY],CATEGORY,SUBCATEGORY,[PRICE TIER],SIZE,key)
/
(aggr(nodistinct max({1<Market={'$(possibleMarkets)'},[MEGA CATEGORY]={'$(possibleMegaCategory)'}>}$(metric)),Market,[MEGA CATEGORY],CATEGORY,SUBCATEGORY,[PRICE TIER],SIZE))
/
aggr(nodistinct avg({1<Market={'$(possibleMarkets)'},[MEGA CATEGORY]={'$(possibleMegaCategory)'}>}aggr((sum({1<Market={'$(possibleMarkets)'},[MEGA CATEGORY]={'$(possibleMegaCategory)'}>}$(metric))
*
sum({1<Market={'$(possibleMarkets)'},[MEGA CATEGORY]={'$(possibleMegaCategory)'}>}aggr(rangesum(above(sum({1<Market={'$(possibleMarkets)'},[MEGA CATEGORY]={'$(possibleMegaCategory)'}>}$(metric)),0,rowno())),Market,[MEGA CATEGORY],CATEGORY, SUBCATEGORY,[PRICE TIER],SIZE, key))
/
sum({1<Market={'$(possibleMarkets)'},[MEGA CATEGORY]={'$(possibleMegaCategory)'}>}aggr(nodistinct rank(aggr(nodistinct sum({1<Market={'$(possibleMarkets)'},[MEGA CATEGORY]={'$(possibleMegaCategory)'}>}$(metric)),Market,[MEGA CATEGORY],CATEGORY,SUBCATEGORY,[PRICE TIER],SIZE,key)),Market,[MEGA CATEGORY],CATEGORY,SUBCATEGORY,[PRICE TIER],SIZE,key))
/
(sum({1<Market={'$(possibleMarkets)'},[MEGA CATEGORY]={'$(possibleMegaCategory)'}>}aggr(nodistinct max({1<Market={'$(possibleMarkets)'},[MEGA CATEGORY]={'$(possibleMegaCategory)'}>}$(metric)),Market,[MEGA CATEGORY],CATEGORY,SUBCATEGORY,[PRICE TIER],SIZE)))),Market,[MEGA CATEGORY],CATEGORY,SUBCATEGORY,[PRICE TIER],SIZE,key)),Market,[MEGA CATEGORY],CATEGORY,SUBCATEGORY,[PRICE TIER],SIZE)
*100,'')
// ,Market,[MEGA CATEGORY],CATEGORY,SUBCATEGORY,[PRICE TIER],SIZE,key))
