Forum Discussion
Post PatronPrevious 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!
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
10 Replies
- mahoneypat
Microsoft Employee
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
- amitchandak
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/367415 - tex628
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)- 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!
- Mariusz
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?
