Previous year values for current top 10

[DavidWaters100]

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 get a correct measure working - all I get is top 10 from the previous year, ignoring the top 10 of the current year!

As ever, help greatly appreciated!

Top 10 Brokers = var ranking = values(BrokerNames[Short Name]) return calculate([Current Year Amount],TOPN(10,BrokerNames,[Current Year Amount]),ranking)

[Mariusz]

Hi @DavidWaters100 

Try somthing like below.

Measure = 
VAR __topN = TOPN( 5, ALLSELECTED( 'Product'[ColorName] ), [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]

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]

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?

[DavidWaters100]

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]

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

[Mariusz · Accepted solution]

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

[tex628]

I dont really know how your Current year amount is built but try something along the lines of this:

Top 10 Brokers = 
var ranking = values(BrokerNames[Short Name]) 
return 
Calculate(Calculate([Current Year Amount],SAMEPERIODLASTYEAR(Calendar[Date])),
TOPN(10,BrokerNames,[Current Year Amount]),
ranking)

[DavidWaters100]

Hi @tex628 @mahoneypat 

Thank you for your replies - I tried both of these and they still returned $3.1million per below, which is total for last year, regardless of current top 10.  My current and previous year calculations are working off a linked calendar table and they are working OK.

I am trying to return the 2.276m which is just the amounts for the top 10  of 2020 (there were only 4 types in green).  Your measures look like they should work but they don't seem to!

[amitchandak]

@DavidWaters100 , You might have measure like these

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

YTD QTY = TOTALYTD(Sum('order'[Qty]),'Date'[Date])
LYTD QTY = TOTALYTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year))
Previous Year = CALCULATE(SUM('order'[Qty]), PREVIOUSYEAR('Date'[Date]))

create a rank on this year and filter top 10  or filter using top

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/3...

[mahoneypat]

Please try this approach instead:

Top 10 Brokers = var ranking = values(BrokerNames[Short Name])

var top10CY = calculatetable(values(BrokerNames[Short Name]),TOPN(10,BrokerNames,[Current Year Amount]),ranking)

return calculate([Prev Year Amount], top10CY)

This assume you already have a Prev Year Amount measure.

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat