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

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 🙂