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

     

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!