Return list member with highest value in module on other dimension
I have two modules:
"Module A" with two dimensions: "Product" and "Market"
"Module B" with one dimension: "Product"
Module A has a line item "Sales" that has the sales by Product and Market.
I would like to display the top 3 Markets (formatted with the "Market" list) with the highest sales in Module B, with three line items: "Highest Sales", "2nd Highest Sales", "3rd Highest Sales".
I managed to get the information but not without creating a large number of empty cells which will clutter my model at some point. In Module A I used RANK to display the market's own name if it is the highest selling for this product (or 2nd, 3rd, in separate line items) in its own Product x Market combination:
In short if Rank = 1 then item(Market) else blank (same for Rank = 2 etc) in Module A then creating the same three line items: "Highest Sales", "2nd Highest Sales", "3rd Highest Sales" in Module B with a LOOKUP for Product. So if I have 20 markets, I have 19 out of 20 celles that are empty in this module for each ranking line item: 1st, 2nd and 3rd highest.
Is there another way to get that done without having to keep the formula and the clutter in Module A ?
Thank you very much !