Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have a table with follwing 4 fields
1- CAT
2- SubCAT
3- DetailCAT
4- Sales
I have created a Matrix Table with drill down on Cat--> SubCat-->DetailCat with value is SUM(Sales)
I want to show Top 5 of CAT initially on the Matrix Tablec, then when I drill down to next level (SUBCAT), we want to see TOP 5 of SUBCAT, and then when we drill down to next level, we want to see TOP 5 of DetailCAT
Can anyone please help if this is possible ?
Thanks in Advance.
Ashish
Solved! Go to Solution.
Hi, @ash_cool10
Please refer to the solution mentioned in this thread.
Filtering the Top 3 products for each category in Power BI
Pleae try formulas as below:
Rank2 =
IF (
ISINSCOPE ( 'Table'[DetailCAT] ),
RANKX (
CALCULATETABLE (
VALUES ( 'Table'[DetailCAT] ),
ALLSELECTED ( 'Table'[DetailCAT] )
),
[Sales Amount]
),
IF (
ISINSCOPE ( 'Table'[SubCAT] ),
VAR SubcatSales = [Sales Amount]
RETURN
CALCULATE (
RANKX ( VALUES ( 'Table'[SubCAT] ), [Sales Amount], SubcatSales ),
ALLSELECTED ()
),
IF (
ISINSCOPE ( 'Table'[CAT] ),
VAR CatSales = [Sales Amount]
RETURN
CALCULATE (
RANKX ( VALUES ( 'Table'[CAT] ), [Sales Amount], CatSales ),
ALLSELECTED ()
)
)
)
)
Then filter the data based on the newly created ranking column.
Best Regards,
Community Support Team _ Eason
Hi, @ash_cool10
Please refer to the solution mentioned in this thread.
Filtering the Top 3 products for each category in Power BI
Pleae try formulas as below:
Rank2 =
IF (
ISINSCOPE ( 'Table'[DetailCAT] ),
RANKX (
CALCULATETABLE (
VALUES ( 'Table'[DetailCAT] ),
ALLSELECTED ( 'Table'[DetailCAT] )
),
[Sales Amount]
),
IF (
ISINSCOPE ( 'Table'[SubCAT] ),
VAR SubcatSales = [Sales Amount]
RETURN
CALCULATE (
RANKX ( VALUES ( 'Table'[SubCAT] ), [Sales Amount], SubcatSales ),
ALLSELECTED ()
),
IF (
ISINSCOPE ( 'Table'[CAT] ),
VAR CatSales = [Sales Amount]
RETURN
CALCULATE (
RANKX ( VALUES ( 'Table'[CAT] ), [Sales Amount], CatSales ),
ALLSELECTED ()
)
)
)
)
Then filter the data based on the newly created ranking column.
Best Regards,
Community Support Team _ Eason
MAG1
Consolidated Account Group
Customer Name
Material
We are also facing same problem can any one give solution if any.
User | Count |
---|---|
47 | |
26 | |
21 | |
17 | |
15 |
User | Count |
---|---|
53 | |
34 | |
17 | |
17 | |
15 |