topic Re: TOPN Measure Results in Column Incorrect in DAX Commands and Tips

TOPN Measure Results in Column Incorrect

Anonymous — Tue, 25 May 2021 12:44:08 GMT

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

Re: TOPN Measure Results in Column Incorrect

Jihwan_Kim — Tue, 25 May 2021 13:03:05 GMT

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.

Re: TOPN Measure Results in Column Incorrect

Anonymous — Tue, 25 May 2021 13:08:01 GMT

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

Re: TOPN Measure Results in Column Incorrect

Jihwan_Kim — Tue, 25 May 2021 13:57:18 GMT

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

Re: TOPN Measure Results in Column Incorrect

Anonymous — Tue, 25 May 2021 14:35:51 GMT

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

Re: TOPN Measure Results in Column Incorrect

Jihwan_Kim — Wed, 26 May 2021 18:27:37 GMT

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

Re: TOPN Measure Results in Column Incorrect

Anonymous — Wed, 26 May 2021 23:23:39 GMT

Thank you Jihwan_Kim it worked 🙂