cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DavidWaters100
Post Patron
Post Patron

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 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)
Proud to be a Super User!
1 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

 

View solution in original post

10 REPLIES 10
Mariusz
Community Champion
Community Champion

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

 

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. 

Proud to be a Super User!

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?

Proud to be a Super User!

 
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] )
)
Proud to be a Super User!

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

Proud to be a Super User!

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

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)

Connect on LinkedIn

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!

 

example2.PNG

 

 

Proud to be a Super User!
amitchandak
Super User
Super User

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

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.