Forum Discussion

WishAskedSooner's avatar
WishAskedSooner
Icon for Continued Contributor rankContinued Contributor
2 years ago
Solved

Dividing and Excluding when No Entries

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.

Need Help
  • lbendlin's avatar
    lbendlin
    2 years ago

    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)

     

2 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    2 years ago

    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's avatar
      WishAskedSooner
      Icon for Continued Contributor rankContinued Contributor
      2 years ago

      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!