TOPN Measure Results in Column Incorrect

[Anonymous]

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

[Jihwan_Kim]

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.

[Anonymous]

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 

[Jihwan_Kim]

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

[Anonymous]

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

[Jihwan_Kim · 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.

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

https://www.dropbox.com/s/rg5a19kq6nbh7dp/TopNSales.pbix?dl=0 

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

[Anonymous]

Thank you @Jihwan_Kim  it worked 🙂