topic Re: Distinct group sum in DAX Commands and Tips

Distinct group sum

Kisame01 — Wed, 14 Aug 2019 09:13:12 GMT

Hi Guys I just need the dax measure for the following i need to get the distinct productkey sum(productvalue) is this possible to do it in one measure?

Much appreciated guys

Year	BoxKey	ProductKey	ProductSubcategoryKey	Product value
2016	1	61	1680492	45.57100824
2016	1	61	1876760	45.57100824
2016	1	61	1827408	45.57100824
2016	1	61	36732	45.57100824
2016	1	59	3605489	1.0773288
2016	1	60	1829472	6.42357297
2016	1	60	2023932	6.42357297
2016	1	60	386652	6.42357297
2016	1	61	1732332	45.57100824
2016	1	60	1928892	6.42357297
2016	1	60	1827084	6.42357297
2016	1	60	1727724	6.42357297
2016	1	59	332652	1.0773288

Re: Distinct group sum

Anonymous — Wed, 14 Aug 2019 17:46:10 GMT

Kisame01

A quick question, say looking at ProductKey =59 and Year=2016. What would the answer you are looking for be?

2.154658

Re: Distinct group sum

Anonymous — Wed, 14 Aug 2019 17:53:37 GMT

[Sum of Value] = sum ( T[Product Value] )

This sum will adjust itself as you start slicing and dicing your data.

Best
Darek

Re: Distinct group sum

Kisame01 — Thu, 15 Aug 2019 05:43:56 GMT

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:

Re: Distinct group sum

Ronald123 — Thu, 15 Aug 2019 07:29:05 GMT

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,

Ronald

Re: Distinct group sum

Kisame01 — Thu, 15 Aug 2019 07:57:44 GMT

Hi 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

Re: Distinct group sum

Ronald123 — Thu, 15 Aug 2019 08:14:05 GMT

Kisame01 ,

Check the file.

https://1drv.ms/u/s!An5X7wdH9O9ejFUNADjH3GGxitrW

Greets,

Ronald

Re: Distinct group sum

Kisame01 — Thu, 15 Aug 2019 08:18:40 GMT

Magic Roland. :smileyvery-happy: