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