I have a dataset in which Category, Sub Category and Sales are given.
I wish to create a matrix in which i want to see RANK based Category and Sub category when selected.
I have created one Fact table and 2 Dimention tables (Category table & Sub category table) and established relationships.
I want to see RANKS of each category in Matrix and while expanding the category button, rank should modify based on Sub category within same category.
Please note: sales column is from "Fact table", Sub category column from "Sub category table" and Category column from "Category table" as shown below.