Forum Discussion

Post 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

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

tex628

Community Champion

6 years ago

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

Post Patron

6 years ago

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!