cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Kisame01 Frequent Visitor
Frequent Visitor

Distinct group sum

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

YearBoxKeyProductKeyProductSubcategoryKeyProduct value
2016161168049245.57100824
2016161187676045.57100824
2016161182740845.57100824
20161613673245.57100824
201615936054891.0773288
201616018294726.42357297
201616020239326.42357297
20161603866526.42357297
2016161173233245.57100824
201616019288926.42357297
201616018270846.42357297
201616017277246.42357297
20161593326521.0773288
1 ACCEPTED SOLUTION

Accepted Solutions
Ronald123 Member
Member

Re: Distinct group sum

7 REPLIES 7
Super User I
Super User I

Re: Distinct group sum

@Kisame01 

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

2.154658

 

 

 

Super User IV
Super User IV

Re: Distinct group sum

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

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

Best
Darek
Kisame01 Frequent Visitor
Frequent Visitor

Re: Distinct group sum

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 Smiley Happy

 

Ronald123 Member
Member

Re: Distinct group sum

@Kisame01 ,

 

Something like this?

Naamloos.png

 

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

Kisame01 Frequent Visitor
Frequent Visitor

Re: Distinct group sum

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

 

YearBoxKeyProductKeyProductSubcategoryKeyProduct valueExpected Result 
201615936054891.07732880.5386644 
20161593326521.07732880.53866441.0773288‬
201616018294726.423572971.070595495‬ 
201616020239326.423572971.070595495‬ 
20161603866526.423572971.070595495‬ 
201616019288926.423572971.070595495‬6.42357297
201616018270846.423572971.070595495‬ 
201616017277246.423572971.070595495‬ 
2016161168049245.571008249.114201648‬ 
2016161187676045.571008249.114201648‬ 
2016161182740845.571008249.114201648‬45.57100824
20161613673245.571008249.114201648‬ 
2016161173233245.571008249.114201648‬ 
    Total51.99458121 
Ronald123 Member
Member

Re: Distinct group sum

Kisame01 Frequent Visitor
Frequent Visitor

Re: Distinct group sum

Magic Roland. Smiley Very Happy

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors