Forum Discussion

DavidWaters100's avatar
DavidWaters100
Icon for Post Patron rankPost Patron
6 years ago
Solved

Previous year values for current top 10

Hi,   I am running below measure to return top 10 values for the current year.   I also need to return the values of the previous year - but for the same top 10 in current year.  I can't seem to ...
Need Help
  • Mariusz's avatar
    Mariusz
    6 years ago

    Hi DavidWaters100 

     

    Try if this works for you.

    Measure = 
VAR __selection = ALLSELECTED( 'Product'[ColorName] )
VAR __countSelection = COUNTROWS( __selection )
VAR __logical = IF( __countSelection > 10, 10, __countSelection )
VAR __topN = TOPN( __logical, __selection, [Sales Amount], DESC )
RETURN
CALCULATE( 
    [Sales Amount],
    KEEPFILTERS( __topN ),
    SAMEPERIODLASTYEAR( 'Date'[Datekey] )
)

     

    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

    Please feel free to connect with me.
    LinkedIn

     

DavidWaters100's avatar
DavidWaters100
Icon for Post Patron rankPost Patron
6 years ago

Hi Mariusz  - I tried your measure but I still get the whole total for last year and not just last year for the current year top 10!  That is the 3.1m in my example. 

DavidWaters100's avatar
DavidWaters100
Icon for Post Patron rankPost Patron
6 years ago

Hi Mariusz  - your measure works if I change it to top 4 only.  But the problem is that I have to specify top ten - in most cases (when report filters are applied)  there will be a full top ten but in some cases there are less than 10.  In cases less than 10, the measure still returns a full top 10 for last year.  Can the TopN be dynamic if the number if the number of types is less than ten?  I think that will solve it?

  • Mariusz's avatar
    Mariusz
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hi DavidWaters100 

     

    Try if this works for you.

    Measure = 
VAR __selection = ALLSELECTED( 'Product'[ColorName] )
VAR __countSelection = COUNTROWS( __selection )
VAR __logical = IF( __countSelection > 10, 10, __countSelection )
VAR __topN = TOPN( __logical, __selection, [Sales Amount], DESC )
RETURN
CALCULATE( 
    [Sales Amount],
    KEEPFILTERS( __topN ),
    SAMEPERIODLASTYEAR( 'Date'[Datekey] )
)

     

    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

    Please feel free to connect with me.
    LinkedIn

     

  • DavidWaters100's avatar
    DavidWaters100
    Icon for Post Patron rankPost Patron
    6 years ago
    Hi Mariusz 
     
    I added below in bold, where count top n is a distinct count of how many entries are actually in the top ten.  Where less than 10 it returns the reduced number.  This seems to now work.
     
    Thanks too everyone else who replied - it may be that your methods work too if this "dynamic" N is added in.
     
    Measure =
    VAR __topN = TOPN( if([count top n]<10,[count top n],10), ALLSELECTED( BrokerNames[Short Name] ), [Current Year Amount], DESC )
    RETURN
    CALCULATE(
       [Current Year Amount],
        KEEPFILTERS( __topN ),
        SAMEPERIODLASTYEAR( 'Calendar Table'[Date] )
    )
    • DavidWaters100's avatar
      DavidWaters100
      Icon for Post Patron rankPost Patron
      6 years ago

      Hi Mariusz , thanks I accepted your post as solution, I think we posted at almost same time just now