I have a Product Table, and using properties on the table, I developed level based hierarchy(not manually).
I also have a sales fact table, and partner table.
I have multiple products, and for each product there are multiple partners.
My requirement is to sum the top 10 partners revenue for a product divided by sum of revenue from all partners for that product.
I duplicated the sales column and named as sales by product, when I mapped the sales by product column to product total, it is giving the sum of sales from all products.
But I need sales for each product., when I mapped to the detail level It is drilling the sales by product to a detal level.
Please help m how to map the sales by product column to product hierarchy.
The columns in product table are...product key, product grp-id,product flag, product name( the column to which i need to map)
My hierarchy in RPD is , product dimdim--product Toatl----Product deatil------all the columns in product.
1, sum of revenue from all partners ==> Duplicate sales logical column and set the level for partner and Product.
2, top 10 partners revenue for a product ==> Created another logical column and Use topn function and set the levels for product and partner at detail..