How to group a row and divide values

[NY2BOS]

Hello all my data looks like this 

Sale Order	Product	Value
1	Printer
1	Printer
1	Printer	$6,000
1	Screen
1	Screen
1	Screen
2	Ink
2	Ink	$500
2	Ink

For sales order 2 it makes sense because the ink is $500 and i do a unique view. But for sales order 1 I'm looking to group sales order count unique products then divide the value. In the end printer should have $3,000 and screen Should have $3,000.

Thank you in advance

[tristank]

Hi @NY2BOS 

We can use the summarize tool to aggregate by sale order, find the sum of the value and the count distinct of product. From there we use the formula tool to calculate the sum divided by the distinct count of products. I have attached a picture and a sample workflow for you to download below. Please mark this as a solved solution if this helps and have a great day!

[NY2BOS]

@tristank thank you for the quick reply, using this flow can i get a view that looks like 

Sales Order	Product	Value
1	Printer	3000
1	Printer
1	Printer
1	Screen	3000
1	Screen
1	Screen
2	Ink	500
2	Ink

[tristank · Accepted solution]

You could although I'm curious how that structure would be valuable as it is a bit odd ha! 

1. Use the summarize tool to get the sum of the value grouped by sales order

2. Use the summarize tool to get the number of distinct products grouped by sales order

3. Use the formula tool to calculate the two i.e 6000 / 2 = 3000.

4. Take this data and join it to your original table on sales order

5. Create another formula that says if the value is not null then replace the value with the previous calculation (3000) otherwise leave the value column empty

Hope this helps!

[axb210253]

That is a great explanation, thanks tristank !