How to group a row and divide values

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

Datasets

Tips and Tricks

Accepted answers

tristank

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!

All comments

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!

ny2 example.jpg

summarization_example.yxmd

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

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 !

Quick Links

This months top contributors

SophiaF 432

AkimasaKajitani 390

mceleavey 388

mbarone 337

michael_weaver 335