Topn with additonal filter

[MichalDe]

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.

Top 10 All Time = CALCULATE([Total Sales],

TOPN(10, ALL('Customer'), [Total Sales], DESC),
ALL('Date'[Date])

)

but

Thanks in advance for your help

[Syndicate_Admin]

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 sales

If please tell me what I am doing wrong

Thank you very much

[Anonymous · Accepted solution]

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.

[MichalDe]

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.

[Anonymous]

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.

[MichalDe]

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?

[Anonymous]

Hi @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.

[MichalDe]

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 k

[Anonymous]

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.

[MichalDe]

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.

[MichalDe]

Hi Eyelyn9

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.