Forum Discussion
Filtering Denominator Based on Data From Another Column
Amerivike , Try a measure like
measure =
var _account = selectcolumns(filter(Table, Table[Product] = "Product 6"), "Account",Table[Account])
return
divide(calculate(sum(Table[Ship Volume]),filter(Table, Table[Product] = "Product 6")),calculate(sum(Table[Ship Volume]),filter(Table, Table[Account] in _account)))
Only denominator
measure =
var _account = selectcolumns(filter(Table, Table[Product] = "Product 6"), "Account",Table[Account])
return
calculate(sum(Table[Ship Volume]),filter(Table, Table[Account] in _account))
Thanks! This works great with the exception of the component concerned with when Product 6 was received. The way this calculation works, it brings category volume into the denominator for all weeks on record (even those prior to when the new product was received). The only category volume that I can have in the denominator is the volume for an account after product 6 was received. Every account could receive the new product during different weeks.
Amerivike - Are you still having issues with this or is it resolved? If still having issues, please post expected output from your sample data.
The expected output for Texas, for example, would be all of the Product 6 volume in Texas as the numerator (1/4 account 1 & 1/11 account 3) = 40. Divided by all of the Category A volume that would have been received on or after the initial shipment of product 6 (1/4 Account 1, 1/11 Account 1 & Account 3) = 32+109 + 86 = 227. The share of product 6 in stores selling in Tx would be 40/227 or .176. Since Account 1 & 3 have received product 6, their Category A volume will continue to add to the denomintor every week from that point on. We want to understand how our product compares to the total category on in the stores that are carrying it with the assumption that they will order more of it when they are close to selling out.
