Filtering Denominator Based on Data From Another Column

[Amerivike]

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!

[amitchandak]

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

[Amerivike]

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.

[Greg_Deckler]

@Amerivike - Are you still having issues with this or is it resolved? If still having issues, please post expected output from your sample data.

[Amerivike]

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.

[v-easonf-msft]

Hi, @Amerivike 

Sorry,I'm still confused about your question. Can you explain it in more detail ?

How to calculate  all of the Category A volume that would have been received on or after the initial shipment of product 6.

Why the value corresponding to "Account 3" is 86.

Best Regards,
Community Support Team _ Eason

[Amerivike]

Thanks for the response, but this was moved to another group and apparently there was a resolution.

[v-easonf-msft]

Hi, @Amerivike 

If you have worked it out ,can you accept your reply as  a solution to close this thread?

Thanks.

Best Regards,
Community Support Team _ Eason

[Greg_Deckler]

@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])