Hello,
please look at the attached file. Is there any way to make a conditional grouping?
In this specific case I would like to group all the product codes that have the same product code and sum their quantity in one row if the net purchase price is the same as well as with same sell price.
How to do it? Do I need to do it in pivot table, in data model, power bi or what?
Could you show me how it will be the best way?
Thank you very much in advance.
This proposal converts the range in columns A:E into an Excel table and then adds a column (F).
Column F is populated using: =COUNTIFS([product_code],[@[product_code]],[net_purchase_price],[@[net_purchase_price]],[sell_price],[@[sell_price]])
The pivot table uses the average of column F in the Values area.
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
Hello,
thank you for the solution. I would like to understand why you did it in this way. First of all why you transformed the range in table and what does it mean in the formula COUNTIFS the "@" in front of column name?
The same counfifs formula if used in a normal range cannot be used in the same way? It is only a question to understand why you did these steps.
I made a second sheet with only a range and create the pivot table : it works in the same way I think.
Based of what already has been done I would like in the pivot table to add a custom column which calculates the gross profit for the grouped column. This means :
to group all the product_code articles that have the same number, the same net_puchase_price and same sell_price e in one row summing on a new column the total quantity and the total profit (total quantity*(sell_price-net_purchase_price) respecting the previous statement..
This means that if the product_code or net_purchase_price or the sell_price differ in something to go on the next row and do the same totals.
Thank you
I transformed the range to a table because:
1. As new rows of data are added formulas are copied down automatically
2. The resulting pivot table can be 'Refreshed' to include the new rows of data (no need to change the data source).
Perhaps the following will help explain the use of @ in the formula better than I can: https://support.microsoft.com/en-us/...3-c8ae6d2b276e
That said and looking at the file in post #2 again I realize that the same pivot table could be made without adding the Count column
Using only columns A:E
1. Drag product_code, net_purchase_price and sell_price into the Rows area
2. Drag product_code into the Values area (it should automatically display as count)
To get a column that displays Gross profit add a Calculated Field to the pivot table. The formula for the calculated field is =quantity *(sell_price -net_purchase_price )
To see that it is grouping as you want take a look at rows 73:74 where product code 0501323531 is shown in two rows because the sell_price differs.
Let us know if you have any questions.
Thank you very much for the detailed answer. I have another question on the modified file by you. If I go in the pivot table on the column "Sum of Gross profit" and I click on a cell with the right button of the mouse and I chose sort smallest to largerst or viceversa, it doesn't happen anything. In order to work, I need to remove from the pivotTable Fields in the rows section net_purchase_price and sell_price, do the sort and the put them again. Is it my excel that works in a wrong way or what? Or there is a better way to do it?
I tried several things including the method that you shared and adding a Power Pivot measure. In the end this seemed to work:
Add a column to the source data populated using: =[@[product_code]]&[@[net_purchase_price]]&[@[sell_price]]
Refresh the pivot table and drag the new column (Sort) into the Rows area (above the other fields in that area).
Then select the filter sort button in cell H4 then More Sort Options and then Sort ascending by Sum of Gross Profit.
Note that the Sort column (H) in the pivot table may be hidden for aesthetic purposes.
Let us know if you have any questions.
Hello,
Very interesting the solution of adding a custom column named Sort....but why? It is a bug of excel? This solution is good if I need only one type of sort, but If I need to make sorts with also the others columns it will start to become tricky and longer...anyway I am only curious if it a bug of excel.
Returning to the main issue, for such a reason the the custom Sum of column Gross Profit does not calculate in the right way. Please take as example the product code 0671090384 : you have a net purchase price of 81.8732, a sell price of 100.47 and -18 as total quantity. The calculation is simple ( 100.47-81.8732)*-18= -334.7424 that does not match with -4686.39. the difference is big. Do you have any idea why is it happening?
As for sorting, I don't know whether or not it could be called a bug.
As for the sum, I should have caught that. The reason for the -4686.39 value is that it took -334.7424 and multiplied by the number of rows included raw data that met the condition (see Sheet1). To compensate add another column to the source data (Count) which is populated with 1's. The calculated field is then modified to read: =quantity*(sell_price-net_purchase_price)/Count
Let us know if you have any questions.
Hello,
thank you again for finding a workaround, but it would be good to understand why excel multiply it with the number of rows that met the condition. This is for future purposes, if I need to make other formulas.
Anyway, How to modify the Sort column, in order to sort the product code from the highest value to the lowest one (or viceversa)?
It seems that the calculated field is applied to each row of the data.
We could add a 'Gross' column to the source data using: =[@quantity]*([@[sell_price]]-[@[net_purchase_price]])
We could then use that in the pivot table (see columns P:T) instead of using a calculated field and we would not need the 'Count' column in the source data.
If you want to sort by product code then make product code the top field in the Rows area (see Sheet2)
Note that in this case you would not need the 'Sort' field.
Let us know if you have any questions.
You wrote : "It seems that the calculated field is applied to each row of the data.
We could add a 'Gross' column to the source data using: =[@quantity]*([@[sell_price]]-[@[net_purchase_price]])
We could then use that in the pivot table (see columns P:T) instead of using a calculated field and we would not need the 'Count' column in the source data."
I am fully agreed with you. It is easier and more logic to add a column directly into the table.
You wrote : "If you want to sort by product code then make product code the top field in the Rows area (see Sheet2)
Note that in this case you would not need the 'Sort' field."
Sorry there was probably a misunderstanding of the made question by me. I would like to order the product code starting from the one with the highest gross profit to the one with the lowest gross profit (or viceversa).
On Sheet2 select the filter/sort button in cell A3 > More sort options > Descending (Z to A) by: and in that drop down select Sum of Gross
Note that this will group product codes that have different net_purchase_price and sell_price as shown in rows 45:46
When the values in E45:E46 are summed the result is 0.0528 which is why product code 0731201732 is displayed before 0634313215 which has a gross of zero.
Let us know if you have any questions.
I didn't know for this more sort options...thank you very much for this additional hints.
You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks