Forum Discussion
Nested Top N using Power BI DAX
Hi, rashmi_panicker
Because the context of the matrix is complex, and the value of measure changes according to the results in the filter pane, it is not possible to simply filter.
With my efforts, I made this perfect solution, hope you can give me a kudos.
Total rank measure:
Rank = IF ( ISINSCOPE ( 'Table'[Category] ), RANKX ( FILTER ( ALL ( 'Table' ), [Manufacturer] = SELECTEDVALUE ( 'Table'[Manufacturer] ) ), CALCULATE ( MAX ( 'Table'[Sales] ) ) ) , IF ( ISINSCOPE ( 'Table'[Manufacturer] ), RANKX ( SUMMARIZE ( ALL ( 'Table' ), [Manufacturer], "total", SUM ( 'Table'[Sales] ) ), CALCULATE ( SUM ( 'Table'[Sales] ) ), )) )Column for rank category:
rankcategory = RANKX(FILTER(ALL('Table'),[Manufacturer]=EARLIER('Table'[Manufacturer])),[Sales])I created a column because I can't use two topns in the filter pane.
Below is my sample. If you don't understand anything, feel free to ask me.
Janey
Hi, rashmi_panicker
Because the context of the matrix is complex, and the value of measure changes according to the results in the filter pane, it is not possible to simply filter.
With my efforts, I made this perfect solution, hope you can give me a kudos.
Total rank measure:
Rank =
IF (
ISINSCOPE ( 'Table'[Category] ),
RANKX (
FILTER (
ALL ( 'Table' ),
[Manufacturer] = SELECTEDVALUE ( 'Table'[Manufacturer] )
),
CALCULATE ( MAX ( 'Table'[Sales] ) )
)
,
IF (
ISINSCOPE ( 'Table'[Manufacturer] ),
RANKX (
SUMMARIZE ( ALL ( 'Table' ), [Manufacturer], "total", SUM ( 'Table'[Sales] ) ),
CALCULATE ( SUM ( 'Table'[Sales] ) ),
))
)
Column for rank category:
rankcategory = RANKX(FILTER(ALL('Table'),[Manufacturer]=EARLIER('Table'[Manufacturer])),[Sales])
I created a column because I can't use two topns in the filter pane.
Below is my sample. If you don't understand anything, feel free to ask me.
Janey
Thank you Janey for your response. The solution that you provided is perfect but I am not able to go beyond this point. We are able to get the result in a Matrix. Now, how do we split these 3 Manufacturers on 3 different graphs displaying their own respective top 4 categories and their sales?
Hi, rashmi_panicker
Create two columns for rank and use it in filter pane. Set the rankmanufacturer to 1, 2, and 3 in the three graphs respectively.
rankManufacturer = RANKX ( SUMMARIZE ( ALL ( 'Table' ), [Manufacturer], "total", SUM ( 'Table'[Sales] ) ), SUMX(FILTER('Table',[Manufacturer]=EARLIER('Table'[Manufacturer])),[Sales]))Janey
Hello Janey,
Your solution will definitely work in case of the sample shared by me. But, my actual table has many other columns and my report has multiple slicers. When I try to add a column in this table for rank, I am getting the result as 3 & 4 digit numbers instead of 1,2,3, etc. How do I modify the DAX expression for rank column to incorporate this scenario?
Hi, rashmi_panicker
Due to your special needs, I can only use calculated column, because it doesn't change with the context like measure, but requires more memory.
What's mean about 'I am getting the result as 3 & 4 digit numbers instead of 1,2,3, etc'? I don't know your real scenario or how you modified my code in your report, how else can I help you?
Janey