Dividing and Excluding when No Entries

[WishAskedSooner]

I have a narrow fact table similar to the following:

It is joined on the following dimension table:

My goal is to calculate the average Discount by Quantity, however making sure not to include Quantities for which there is no Discount (or Discount is zero). For example:

(10 + 20 + 30) / (40 + 50 + 70) = 60 / 160 = 0.375

So, as you can see, since OID 3 does not have a Discount, I need to exclude it from the divisor sum. Alternatively, OID 3 could potentially have an entry for Discount equal to zero in which case I still want to exclude it.

Thanks in advance for the help.

[lbendlin · Accepted solution]

Avg Disc By Qty = 
var a = intersect(SELECTCOLUMNS(filter('Table',[AID]=1),[OID]),SELECTCOLUMNS(filter('Table',[AID]=2),[OID]))
return divide(CALCULATE(sum('Table'[VALUE]),'Table'[AID]=1,'Table'[OID] in a),CALCULATE(sum('Table'[VALUE]),'Table'[AID]=2,'Table'[OID] in a),0)

[WishAskedSooner]

@lbendlin Thank you so much for your quick solution! I am not sure why, but division with DAX seems way harder than it should be. Kudos!