Forum Discussion
Topn with additonal filter
Hi
I'm new in Power and I've got a problem with TOPN function with additonal FILTER on Dates. What am I doing wrong in this case?
I want to get the column with TOP 10 customers for all dates (all period) equals $ 135k in each rows next to this column with "Total Sales"
Top 10 customers by " total sales" for all dates (all period - no filters on dates) is as below = $ 135 k
I was tring to get this by using measure like below but no vain.
ALL('Date'[Date])
Thanks in advance for your help
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.
10 Replies
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.Thanks a lot.
Method 1 works perfectly.
Stil wondering why in my measure, All(date[date]) function as a "filter2" embedded in CALCULATE statement doesn't change filter context for year in the raport table.
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.In your file it works, but in my file doesn't,. I do not know what is wrong. Maybe in my example the related table is used but the other hand shoud work anyway.
Additionaly this measure
top 10 customers measure = CALCULATE([Total Sales],TOPN(10, ALL('Customer'),[Total Sales],DESC))yields 132 k and i do not know why this isint't corespoding with 135 kHi MichalDe ,
Please try this:
Top 10 All Time = IF( RANKX(ALL('Internet Sales'),[Total Sales],,DESC,Dense)<=10,[Total Sales])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 used the data sample you provided, was it not your real data ?
Both measures below work fine on my side. Please try :
Top 10 All Time = IF( RANKX(ALL('Date'),[Total Sales],,DESC,Dense)<=10,[Total Sales])Top 10 All Time = CALCULATE([Total Sales],TOPN(10,ALL(Customer[Full Name]),[Total Sales],DESC),ALL('Date'[Year]))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 Anonymous
Thank for your reply. Could you send me your screenshot with the table to see how it looks?In my file it still doesnt work. To be more precise I send the table preview that I would like to achieve. Does it look the same on your side?
Syndicate_AdminAdministrator
Hello good day
I am analyzing the sales by Store and the Top product of each.
When you filter by month, it gives the correct result (verified with the table below filtering each store)
But when I filter for April, June and December I get an error
I have these filters applied
the TopN dax is:
TOP Product =CALCULATE(TOPN(1,VALUES(Items[Item Name]),[Total Sales wc]),ALLSELECTED(Stores[Store Name]), VALUES(Stores[State]), ALLSELECTED('Date'))
The sales dax is:Total Sales wc =SUMX(Sales, Sales[Bottles Sold] *RELATED(Items[Bottle Sale]))
The truth is I do not know what happens, every month has salesIf please tell me what I am doing wrong
Thank you very much
