Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
40 | |
20 | |
19 | |
15 | |
15 |
User | Count |
---|---|
47 | |
28 | |
25 | |
17 | |
17 |