Forum Discussion
Distinct group sum
A quick question, say looking at ProductKey =59 and Year=2016. What would the answer you are looking for be?
| 2.154658 |
Hi Nick_M, thanks for the response
Maybe my question wasn't to clear if you look at the product key and the product value for those product keys are exactly the same.
So i want to be able to actually group all of the productkeys together and devide the product value by the number of ProductSubCategoryKeys because. Looking at Productkey 59 both subcatecorykeys '3605489' and '332652' make up the productvalue of '1.0773288' so i need a new column with new values so I can get the true sum of the value.
Nick_M I hope I am making sense, or I am not even sure if I am over complicating things.
Thanks again :smileyhappy:
Ronald123Resolver III
Kisame01 ,
Something like this?
Calculated column
Expected Result = DIVIDE( CALCULATE( SUM('Table'[ProductSubcategoryKey]); FILTER('Table'; 'Table'[ProductKey]=EARLIER('Table'[ProductKey]))); CALCULATE( COUNT('Table'[ProductKey]); FILTER('Table'; 'Table'[ProductKey]=EARLIER('Table'[ProductKey]))))Greets,
RonaldHi Roland,
Not too sure what happens here. But I basically just need in this example Productkey 59's Product value will be 1.0773288/2 because there are 2 product keys of 59. The 1.0773288 is the full amount for the productkey 59, If that makes sense.
So this is what my epected result should be. If it makes sense
Year BoxKey ProductKey ProductSubcategoryKey Product value Expected Result 2016 1 59 3605489 1.0773288 0.5386644 2016 1 59 332652 1.0773288 0.5386644 1.0773288 2016 1 60 1829472 6.42357297 1.070595495 2016 1 60 2023932 6.42357297 1.070595495 2016 1 60 386652 6.42357297 1.070595495 2016 1 60 1928892 6.42357297 1.070595495 6.42357297 2016 1 60 1827084 6.42357297 1.070595495 2016 1 60 1727724 6.42357297 1.070595495 2016 1 61 1680492 45.57100824 9.114201648 2016 1 61 1876760 45.57100824 9.114201648 2016 1 61 1827408 45.57100824 9.114201648 45.57100824 2016 1 61 36732 45.57100824 9.114201648 2016 1 61 1732332 45.57100824 9.114201648 Total 51.99458121 - Ronald123