I hope everyone that's reading this message is having an wonderful day I'm running into a problem, that I would really appreciate your help :slightly_smiling_face:

I have a dataset in PBI, where I have to Rank per 3 different LEVELS of granularity (Cat1, Cat2, Cat3).

WHAT I WANT?

Sales Rank per Category 1 and Brand:

• I want to rank by the total Sales (Brand in Category 1) / Total Sales (Cat1)

Sales Rank per Category 2 and Brand:

• I want to rank by the total Sales (Brand in Category 2) / Total Sales (Cat2)

Sales Rank per Category 3 and Brand:

• I want to rank by the total Sales (Brand in Category 3) / Total Sales (Cat3)

Explaining further:

Example 1:

Having Category 1 filtered, Category 2 filtered and Category 3 filtered I want to:

Rank by the Sales of each Brand in each Category 3 Item / Total Sales(Cat3 item)

Columns Cat1, Cat2 and Cat3 are filtered in the Matrix

1. Company ABC has 1000$ in Sales in the segment from Cat1 Beauty and segment from Cat2 Body and segment from Cat3 Body Milk
2. Company XYZ has 800$ in Sales in the segment from Cat1 Beauty and segment from Cat2 Body and segment from Cat3 Body Milk
3. Company DZA has 200$ in Sales in the segment from Cat1 Beauty and segment from Cat2 Body and segment from Cat3 Body Milk

Find in BOLD the expected OUTPUT

Expected Output:

Example 2:

Having Category 1 filtered, Category 2 filtered and Category 3 NOT filtered I want to:

Rank by the Sales of each Brand for each Category 2 Item / Total Sales(Cat2 item)

Example:

Columns Cat1, Cat2 are filtered in the Matrix

1. Company ABC has 1000$ in Sales in the segment from Cat1 Beauty and segment from Cat2 Body
2. Company ABC has 300$ in Sales in the segment from Cat1 Beauty and segment from Cat2 Hair
3. Company XYZ has 800$ in Sales in the segment from Cat1 Beauty and segment from Cat2 Body
4. Company XYZ has 500$ in Sales in the segment from Cat1 Beauty and segment from Cat2 Hair
5. Company DZA has 200$ in Sales in the segment from Cat1 Beauty and segment from Cat2 Body
6. Company DZA has 200$ in Sales in the segment from Cat1 Beauty and segment from Cat2 Hair
7. Company DZA has 250$ in Sales in the segment from Cat1 Beauty and segment from Cat2 Hands

Find in BOLD the expected OUTPUT

Expected Output:

Example 3:

Having Category 1 filtered, Category 2 NOT FILTERED and Category 3 NOT FILTERED I want to:

Rank by the Sales of each Brand for each Category 1 Item / Total Sales(Cat1 item)

Example:

1. Company ABC has 1000$ in Sales in the segment from Cat1 Beauty
2. Company ABC has 300$ in Sales in the segment from Cat1 Home
3. Company ABC has 300$ in Sales in the segment from Cat1 Men's Clothing
4. Company XYZ has 800$ in Sales in the segment from Cat1 Beauty
5. Company XYZ has 500$ in Sales in the segment from Cat1 Home
6. Company XYZ has 500$ in Sales in the segment from Cat1 Men's Clothing
7. Company DZA has 200$ in Sales in the segment from Cat1 Beauty
8. Company DZA has 200$ in Sales in the segment from Cat1 Home
9. Company DZA has 200$ in Sales in the segment from Cat1 Men's Clothing
10. Company DZA has 200$ in Sales in the segment from Cat1 Women's Clothing

Find in BOLD the expected OUTPUT

Expected Output:

What I need from you?

1. The Measure Market Share Volume % is well built? I Want that the measure calculates based in the Cat filtered

MS Volume % = DIVIDE ( CALCULATE ( SUM(FACT_SALES[SALES_QTY] )), CALCULATE ( SUM (FACT_SALES[SALES_QTY] ), ALLEXCEPT (DIM_PRODUCT,DIM_PRODUCT[Cat1],DIM_PRODUCT[Cat2],DIM_PRODUCT[Cat3] ) ),

2. What could be an example formula to give me what I want? I've tried with RANKX several times, but not seeing the expected results (you can find Rank MS % Measure that i'm trying to build in the link sent)

3. Any other suggestion to produce a similar output but with another structure?

DUMMY DATA:

Please find in the link [PBI + Dataset] PBI + Dataset

Thank you very much

Diego