Forum Discussion
Topn with additonal filter
Hi MichalDe ,
Got it. I have reached your expected in two ways please check.
- Method 1:
1. Create a table:
Sum top10 table = SUMMARIZE('Date',"Top10", SUMX(TOPN(10, ALL(Customer[Full Name]),[Total Sales],DESC),[Total Sales]) )2. Create a measure:
Top 10 All Time = IF([Total Sales]=BLANK(),BLANK(),MAX('Sum top10 table'[Top10]))- Method 2:
1.Create a new Year table:
New Table = SELECTCOLUMNS(FILTER('Date',[Total Sales]<>BLANK()),"Year",[Year])2.Create new total sales measure:
new total sales = CALCULATE([Total Sales],FILTER('Date',[Year]=MAX('New Table'[Year])))3. Top10:
new top10 = SUMX(TOPN(10,ALL(Customer[Full Name]),[Total Sales],DESC),[Total Sales])The final output is shown below:
In addition, when I open your pbix file, an alert dialog shown :
It seems that your PBI is in an earlier version, so please upgrade it to the latest version and have a try.
Download Microsoft Power BI Desktop from Official Microsoft Download Center
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi MichalDe ,
I have built a data sample to easily understand:
Actually I need more details such your tables ,your expected output... Can you please share more information or your pbix file after removing sensitive data to help us clarify your scenario?
In case ,below is how to rank by type,maybe could help you a little.
= RANKX (
FILTER ( ALL ( 'Table' ), 'Table'[Type] = MAX ( 'Table'[Type]) ),
CALCULATE ( MAX ( ( 'Table'[Value]) ) ),
,
DESC
)
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi
Thanks a lot for your reply.
The file I'm working on is the workshop file so no sensitive data.
Please find attached file and look at "Problem" worksheet".
https://1drv.ms/u/s!Ak4rIGHJyQtTiHPu4ck0LGNeb2ML?e=row8sU
Thanks in advance for your reply.