How to calculate Top 10 TY vs Top 10 LY

[Anonymous]

Hi there,

I'm having difficulty finding a way to calcuate the top 10 results (based on slicers applied in the report) at category level for This year (TY) and Last Year (LY).

One method I've used to get the top 10 at style level for TY & LY is to apply a filter on the 'Cards', however I'm unable to work out the variances between the two results.

 How do i calculate the top 10 styles for TY & LY plus the variance between them both?

Example of table, highlighting you can have the same style on multiple rows.

 

Thanks!

[v-lili6-msft]

HI  @Anonymous 

For your case, you need to create a rank first, the create two measure as below

Top 10 TY=IF([Rank]<=10,[TY Measure])

Top 10 LY=IF([Rank]<=10,[LY Measure])

Then create a variance measure

variance =[Top 10 TY] -[Top 10 LY]

Here is a similar post, please refer to it

If you still have the problem, please share some sample data and your expected output.

Regards,

Lin

[Ashish_Mathur]

Hi,

Please share more details.  Share a sample data for 2 years and show the exact result that you are expecting?

[amitchandak]

@Anonymous , hope you are getting you TY and LY with a date or year calendar like

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


YTD QTY forced= 
var _max = today()
return
calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),'Date'[Date]<=_max)
//calculate(TOTALYTD(Sum('order'[Qty]),'Date'[Date]),filter('Date','Date'[Date]<=_max))

LYTD QTY forced= 
var _max = date(year(today())-1,month(today()),day(today()))
return
CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max)
//TOTALYTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year),'Date'[Date]<=_max)


YTD QTY forced= 
var _max = maxx('order',[Order date])
return
calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),'Date'[Date]<=_max)
//calculate(TOTALYTD(Sum('order'[Qty]),'Date'[Date]),filter('Date','Date'[Date]<=_max))

LYTD QTY forced= 
var _max1 =maxx('order',[Order date])
var _max = date(year(_max1)-1,month(_max1),day(_max1))
return
CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max)
//TOTALYTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year),'Date'[Date]<=_max)

Now use the visual level filter and filter top for both

[Anonymous]

@amitchandak - thanksfor the reply.

However, how do I calculate the variance between them both?