Forum Discussion
Filtering Denominator Based on Data From Another Column
I have a dataset that looks like this:
| State | Ship Date | Account | Category | Product | Ship Volume |
| TX | 1/4/2020 | Account 1 | Category A | Product 6 | 5 |
| TX | 1/4/2020 | Account 1 | Category A | Product 4 | 12 |
| TX | 1/4/2020 | Account 1 | Category A | Product 2 | 15 |
| FL | 1/4/2020 | Account 2 | Category B | Product 3 | 26 |
| FL | 1/4/2020 | Account 2 | Category A | Product 2 | 12 |
| FL | 1/4/2020 | Account 2 | Category B | Product 4 | 56 |
| TX | 1/4/2020 | Account 3 | Category A | Product 5 | 15 |
| TX | 1/4/2020 | Account 3 | Category A | Product 4 | 23 |
| TX | 1/4/2020 | Account 3 | Category B | Product 1 | 15 |
| TX | 1/11/2020 | Account 1 | Category B | Product 1 | 78 |
| TX | 1/11/2020 | Account 1 | Category A | Product 4 | 32 |
| TX | 1/11/2020 | Account 1 | Category A | Product 2 | 45 |
| FL | 1/11/2020 | Account 2 | Category A | Product 6 | 12 |
| FL | 1/11/2020 | Account 2 | Category B | Product 2 | 23 |
| FL | 1/11/2020 | Account 2 | Category A | Product 4 | 65 |
| TX | 1/11/2020 | Account 3 | Category A | Product 5 | 49 |
| TX | 1/11/2020 | Account 3 | Category B | Product 6 | 35 |
| TX | 1/11/2020 | Account 3 | Category A | Product 1 | 37 |
Product 6 is a new product in Category A and I want to be able to calculate it's share of Category A only in accounts that have received it. For instance Account 1 received Product 6 on 1/4/20. The shipment volume was 5. The total category volume would be 109 (Account 1's Category A volume for the week of 1/4 & the week of 1/11 since this is a year to date number). I know the first two parts of this calculation : Divide( Calculate(Sum(ship volume),Product = Product 6), Calculate(Sum(Ship volume),Category =Category A).
I need help limiting the Denominator to only the accounts that have received the new product and I need to be able to do this in a way that works with a Timeline Toggle (so it can't be a static table). The denominator would include all of Category A's volume from the week they received it to the end of the time period regardless of whether they received it again.
Product Share of Category in Stores Selling the Product is how we refer to it.
Thanks so much!
8 Replies
Amerivike - If I am following this correctly, maybe:
Denominator = VAR __Rows = FILTER('Table',[Product] = "Product 6") VAR __Accounts = DISTINCT(SELECTCOLUMNS(__Table,"Account",[Account])) VAR __Table = FILTER('Table',[Account] IN __Accounts) RETURN SUMX(__Table,[Ship Volumn])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.
