I have a formula that I have successfully implemented in one of my dashboards to find the BIC value by partner. I tried to take this same formula and place it in a new dashboard (changing out the appropriate fields, and it will not work. Each portion of the formula works independantly, but when placed together it does not give me the right number.
max({1} aggr(sum({1<[Multi Year Term] = {'1 Year'}>} [TOTAL BOOKED ($)]), [Domestic Ultimate Name]),(floor(count({1<[Multi Year Term] = {'1 Year'}>} distinct if([TOTAL BOOKED ($)]<>0,[Domestic Ultimate Name]))*.1)))
Basically, I need to return the bottom value of the top 10% of the total revenue by partner. This formula works perfectly in my other dashboard, but fails each time I try it here. The specific problem is in the rank for the Max function. When I take the floor equation out, and just enter 7 (which is the value it ends up with in this case), it gives me the total revenue for the 7th highest value in the list. I also tried to put the floor equation into another column (where the formula works and gives me 7). I then referenced that column in the BIC formula, but it does not use that value to give me the max, it just gives me the highest value, not the 7th highest.
Does anyone see what I may have missed?
Thanks
