Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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 @ The expected results are in the Top Rece...
  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

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

    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's avatar
Anonymous
Not applicable
5 years ago

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's avatar
Jihwan_Kim
Super User
5 years ago

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's avatar
    Anonymous
    Not applicable
    5 years ago

    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's avatar
      Jihwan_Kim
      Super User
      5 years ago

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

      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's avatar
        Anonymous
        Not applicable
        5 years ago

        Thank you Jihwan_Kim  it worked 🙂