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
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