MAT Total + Top N

[ninnartx]

Hi everyone,

I have a table showing MAT sales of my Top 3 products on the left. I would like to add another column - total sales - to show the total sales in that period regardless of product (but only for my top 3 products). However, when I added TOTAL to my formula, it disregards my set analysis for Top 3 products and "Product B" appeared, as seen on table on the right. How do I get rid of the highlighted rows?

I've attached a sample app. MAT has to be calculated on the frontend because I would like the period to be flexible + users may make other selections.  Any help would be greatly appreciated!!!

[Neymar_Jr]

Hi,

See if this works for you.

Thanks,

RT

[ninnartx]

Yes it did, thank you!

Just out of curiosity, since the IF command affects performance, do you think there's any other way to achieve the same result without sacrificing performance?

[vinieme12 · Accepted solution]

You just need to restrict your dimensions by placing the Rank condition of Top 3 outside your Pick Match

=if(AGGR(Rank(Sum(Value))<=3,_RollingMAT,Product)

,Pick(_RollingMAT,

sum(TOTAL <Yr_Mth> {<Yr_Mth={">=$(=addmonths(max(Yr_Mth),-23))<=$(=addmonths(max(Yr_Mth),-12))"},

Product={"=Rank(Sum(Value))<=3"}>}Value),

sum(TOTAL {<Yr_Mth={">=$(=addmonths(max(Yr_Mth),-20))<=$(=addmonths(max(Yr_Mth),-9))"},

Product={"=Rank(Sum(Value))<=3"}>}Value),

sum(TOTAL {<Yr_Mth={">=$(=addmonths(max(Yr_Mth),-17))<=$(=addmonths(max(Yr_Mth),-6))"},

Product={"=Rank(Sum(Value))<=3"}>}Value),

sum(TOTAL {<Yr_Mth={">=$(=addmonths(max(Yr_Mth),-14))<=$(=addmonths(max(Yr_Mth),-3))"},

Product={"=Rank(Sum(Value))<=3"}>}Value),

sum(TOTAL {<Yr_Mth={">=$(=addmonths(max(Yr_Mth),-11))<=$(=addmonths(max(Yr_Mth),0))"},

Product={"=Rank(Sum(Value))<=3"}>}Value))

)

[vinieme12]

See attached app, use your existing expression with Calculated dimension, so you won't have to add the Rank restriction condition to each of your expressions