Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

TOPN Measure Results in Column Incorrect

Hi

I'm trying to calculate the top 10 sales in a 3month period grouped by the salesperson,state and customer. Here's a link to the pbix TopNSalesRecent @Recent10Sales.PNG The expected results are in the Top Recent Sales Column (hard coded values to show example of what is expected, circled in Blue). My measure is Measure for Top Recent Sales (circled in Red). My measure is 

 

Measure for Top Recent Sales = 
var TopNo = 10
var DaysBefore = -90
var NoOfSales = [Sales]
var LastDatePeriod = LASTDATE(DimDates[Date])
VAR Period = DATESINPERIOD(DimDates[Date],LastDatePeriod,DaysBefore,DAY)
VAR FirstDatePeriod = MINX(Period,DimDates[Date])
 
VAR Result =
CALCULATETABLE(
    TOPN(
        TopNo,
        SUMMARIZE(
            data,
          DimDates[Date],
            data[Customer ID],
            data[Salesperson ID],
            data[State],
            "NoOfSales",NoOfSales
        ),
       DimDates[Date]
        ,DESC
    ),
    FILTER(ALL(DimDates[Date]),DimDates[Date]>= FirstDatePeriod && DimDates[Date] <= LastDatePeriod),
    FILTER(data,NoOfSales>0)
)


RETURN
IF(FirstDatePeriod <= LastDatePeriod,
SUMX(Result,[NoOfSales]))

 

@Jihwan_Kim here is the question I had

1 ACCEPTED SOLUTION

@Anonymous 

Hi,

Please try the below.

I am not sure whether it gives the correct result or not, but I think you can try to create similar to the below measures.

 

Picture4.png

 

Recent 10 dates rank =
VAR currentsalesp =
MAX ( data[Salesperson ID] )
VAR currentcustomer =
MAX ( data[Customer ID] )
VAR currentstate =
MAX ( data[State] )
VAR rankrecent10dates =
RANKX (
FILTER (
ALLSELECTED ( data ),
data[Salesperson ID] = currentsalesp
&& data[Customer ID] = currentcustomer
&& data[State] = currentstate
),
CALCULATE ( MAX ( data[Sales Date] ) ),
,
DESC
)
RETURN
IF ( NOT ISBLANK ( [Sales] ), rankrecent10dates )
 
Recent 10 dates Sales =
VAR newtable =
FILTER (
SUMMARIZE (
ALLSELECTED ( DimDates ),
DimDates[Date],
"@sales", [Sales],
"@rank", [Recent 10 dates rank]
),
[@rank] <= 10
)
RETURN
IF ( NOT ISBLANK ( [Sales] ), SUMX ( newtable, [@sales] ) )
 
 

Hi, My name is Jihwan Kim.


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.


Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM


If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here to visit my LinkedIn page

Click here to schedule a short Teams meeting to discuss your question.

View solution in original post

6 REPLIES 6
Jihwan_Kim
Super User
Super User

Hi, @Anonymous 

May I ask how do you calculate the blue color circle?

For instance, I want to know how to calculate to have the result of 50 for April 1st 2020.


If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here to visit my LinkedIn page

Click here to schedule a short Teams meeting to discuss your question.
Anonymous
Not applicable

I calculate that by adding the latest 10 sales which is 50 its the total from 12 March to April 1st 2020 grouped by that salesperson,customer and state. I hardcoded in the Excel source data file to give example of how the measure should look like 

Hi, @Anonymous 

I still cannot understand why the 31st March's result is also 50.

I tried to create a measure based on my assumption.

Please check the below.

 

Measure for Top Recent Sales =
VAR currentsalesperson =
MAX ( data[Salesperson ID] )
VAR currentcustomer =
MAX ( data[Customer ID] )
VAR currentstate =
MAX ( data[State] )
VAR currentdate =
MAX ( DimDates[Date] )
VAR result =
SUMX (
TOPN (
10,
FILTER (
ALL ( data ),
data[Sales Date] <= currentdate
&& data[Salesperson ID] = currentsalesperson
&& data[Customer ID] = currentcustomer
&& data[State] = currentstate
),
CALCULATE ( MAX ( data[Sales Date] ) ), DESC
),
[Sales]
)
RETURN
result
 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here to visit my LinkedIn page

Click here to schedule a short Teams meeting to discuss your question.
Anonymous
Not applicable

All sales which fall between 12 March and 1 April form part of the latest 10 sales which if all sales are summed up between that range is 50

@Anonymous 

Hi,

Please try the below.

I am not sure whether it gives the correct result or not, but I think you can try to create similar to the below measures.

 

Picture4.png

 

Recent 10 dates rank =
VAR currentsalesp =
MAX ( data[Salesperson ID] )
VAR currentcustomer =
MAX ( data[Customer ID] )
VAR currentstate =
MAX ( data[State] )
VAR rankrecent10dates =
RANKX (
FILTER (
ALLSELECTED ( data ),
data[Salesperson ID] = currentsalesp
&& data[Customer ID] = currentcustomer
&& data[State] = currentstate
),
CALCULATE ( MAX ( data[Sales Date] ) ),
,
DESC
)
RETURN
IF ( NOT ISBLANK ( [Sales] ), rankrecent10dates )
 
Recent 10 dates Sales =
VAR newtable =
FILTER (
SUMMARIZE (
ALLSELECTED ( DimDates ),
DimDates[Date],
"@sales", [Sales],
"@rank", [Recent 10 dates rank]
),
[@rank] <= 10
)
RETURN
IF ( NOT ISBLANK ( [Sales] ), SUMX ( newtable, [@sales] ) )
 
 

Hi, My name is Jihwan Kim.


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.


Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM


If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here to visit my LinkedIn page

Click here to schedule a short Teams meeting to discuss your question.
Anonymous
Not applicable

Thank you @Jihwan_Kim  it worked 🙂

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.