How to determine the effect on a total figure from a single product by excluding it

[DaveCor]

Hi folks, I am trying to determine the effect a single item has on the overall totals if I excluded it from last years total and this years total and divided those figures into each other. 

I have 3 tables

The first is a returns table

Date	Return ID	Product Name
01-Oct-2019	12358	Apple
05-Dec-2019	26959	Grape
28-Jan-2020	32659	Banana
09-Jun-2020	65323	Grape

The second is a Sales table

Date	Product Name	Sale Qty
01-Sep-2019	Apple	1
10-Oct-2019	Grape	1
10-Mar-2020	Apple	3
27-May-2020	Grape	4
06-Jul-2020	Banana	2

The last is a Calendar table 

Returns measure is - 

Total Returns = DISTINCTCOUNT('returns_table'[Return ID])

Sales measure is - 

Total Sales = Sum('Sales_table'[Sale Qty])

My "return rate", which is returns per million, is calculated using -

Return Rate = 1000000 * DISTINCTCOUNT(returns_table[Return ID])/SUM(Sales_table[Sale Qty])

I can calculate last year & this years Return rates using the following 2 measures

Return Rate YTD = 1000000* DIVIDE(

TOTALYTD(COUNT(returns_table[Return ID]),'Calendar'[Date]),TOTALYTD(SUM(Sales_table[Sale Qty]),'Calendar'[Date]) )

Return Rate YTD LY = CALCULATE([Return Rate],PREVIOUSYEAR('Calendar'[Date]))

I can then determine a ratio as 

Ratio = [Return Rate YTD]/[Return Rate YTD LY]

I end up with something like this

Product Name	Total Returns	Total Sale	Return Rate	Return Rate YTD	Return Rate LY	Adjusted Return Rate YTD	Adjusted Return Rate YTD LY	Adjusted Ratio
Apple	X	X	X	X	X	?	?	?
Grape	x	x	x	x	x	?	?	?
Banana	x	x	x	x	x	?	?	?

Here is where I am getting stuck. The "Adjusted" Return Rates show the overall total Return Rate if the returns and sales for a given product was removed from the overall total. As an example, the Adjusted Return Rate YTD figure for Apple would be something like this:

(Total Returns for this year - Apple returns)/(Total sales for this year - Apple Sales)*1000000

I can't figure this one out though, some help would be appreciated

[DaveCor]

Hi @amitchandak  I was looking over your suggestion again and I played with it a bit to try get it to work but I'm still having no luck getting it to return a value

This was the only attempt of mine that didn't throw up errors but still doesn't return any values

Adjusted Return Rate YTD = 1000000 * DIVIDE(

(calculate([Total Returns],datesytd('Calendar'[Date]), all(returns_table)) - calculate([Total Returns],datesytd('Calendar'[Date]))),

(calculate([Total Sales],datesytd('Calendar'[Date]), all(Sales_table)) - calculate([Total Sales],datesytd('Calendar'[Date])))

)

Really hoping someone can figure this one out as, I'm sad to say, this has me beat  

[amitchandak]

@DaveCor , Try like

divide(
calculate([Total Returns],datesytd('Calendar'[Date]), all(returns_table)) - calculate([Total Returns],datesytd('Calendar'[Date])),
calculate([Total Sales ],datesytd('Calendar'[Date]), all(Sales_table)) - calculate([Total Sales ],datesytd('Calendar'[Date]))
)

[DaveCor]

Hi @amitchandak , thank you, I appreciate you taking the time to look at this.

I tried your suggestion but I am getting no values when I add it to my table, just a blank column.