Summing Unique Values

[JEB85]

Hi Guys,

I have a data set within Powerpivot - a simple sample extract can be seen below:

[TABLE="width: 352"]
<TBODY>[TR]
[TD]Contract</SPAN>
[/TD]
[TD]Region</SPAN>
[/TD]
[TD]Value</SPAN>
[/TD]
[TD]Number</SPAN>
[/TD]
[TD]QTR End</SPAN>
[/TD]
[/TR]
[TR]
[TD]BBB</SPAN>
[/TD]
[TD]West</SPAN>
[/TD]
[TD]1349</SPAN>
[/TD]
[TD]5</SPAN>
[/TD]
[TD="align: right"]30/09/2012</SPAN>
[/TD]
[/TR]
[TR]
[TD]BBB</SPAN>
[/TD]
[TD]North</SPAN>
[/TD]
[TD]571</SPAN>
[/TD]
[TD]3</SPAN>
[/TD]
[TD="align: right"]30/09/2012</SPAN>
[/TD]
[/TR]
[TR]
[TD]BBB</SPAN>
[/TD]
[TD]East</SPAN>
[/TD]
[TD]1945</SPAN>
[/TD]
[TD]2</SPAN>
[/TD]
[TD="align: right"]30/09/2012</SPAN>
[/TD]
[/TR]
[TR]
[TD]BBB</SPAN>
[/TD]
[TD]West</SPAN>
[/TD]
[TD]1346</SPAN>
[/TD]
[TD]5</SPAN>
[/TD]
[TD="align: right"]30/09/2012</SPAN>
[/TD]
[/TR]
[TR]
[TD]BBB</SPAN>
[/TD]
[TD]North</SPAN>
[/TD]
[TD]664</SPAN>
[/TD]
[TD]3</SPAN>
[/TD]
[TD="align: right"]30/09/2012</SPAN>
[/TD]
[/TR]
[TR]
[TD]BBB</SPAN>
[/TD]
[TD]East</SPAN>
[/TD]
[TD]1956</SPAN>
[/TD]
[TD]2</SPAN>
[/TD]
[TD="align: right"]30/09/2012</SPAN>
[/TD]
[/TR]
[TR]
[TD]BBB</SPAN>
[/TD]
[TD]South</SPAN>
[/TD]
[TD]781</SPAN>
[/TD]
[TD]6</SPAN>
[/TD]
[TD="align: right"]30/09/2012</SPAN>
[/TD]
[/TR]
[TR]
[TD]BBB</SPAN>
[/TD]
[TD]West</SPAN>
[/TD]
[TD]754</SPAN>
[/TD]
[TD]5</SPAN>
[/TD]
[TD="align: right"]31/12/2012</SPAN>
[/TD]
[/TR]
[TR]
[TD]BBB</SPAN>
[/TD]
[TD]North</SPAN>
[/TD]
[TD]662</SPAN>
[/TD]
[TD]4</SPAN>
[/TD]
[TD="align: right"]31/12/2012</SPAN>
[/TD]
[/TR]
[TR]
[TD]BBB</SPAN>
[/TD]
[TD]East</SPAN>
[/TD]
[TD]586</SPAN>
[/TD]
[TD]1</SPAN>
[/TD]
[TD="align: right"]31/12/2012</SPAN>
[/TD]
[/TR]
[TR]
[TD]BBB</SPAN>
[/TD]
[TD]West</SPAN>
[/TD]
[TD]118</SPAN>
[/TD]
[TD]5</SPAN>
[/TD]
[TD="align: right"]31/12/2012</SPAN>
[/TD]
[/TR]
[TR]
[TD]BBB</SPAN>
[/TD]
[TD]North</SPAN>
[/TD]
[TD]532</SPAN>
[/TD]
[TD]4</SPAN>
[/TD]
[TD="align: right"]31/12/2012</SPAN>
[/TD]
[/TR]
[TR]
[TD]BBB</SPAN>
[/TD]
[TD]East</SPAN>
[/TD]
[TD]512</SPAN>
[/TD]
[TD]1</SPAN>
[/TD]
[TD="align: right"]31/12/2012</SPAN>
[/TD]
[/TR]
[TR]
[TD]BBB</SPAN>
[/TD]
[TD]South</SPAN>
[/TD]
[TD]473</SPAN>
[/TD]
[TD]9</SPAN>
[/TD]
[TD="align: right"]31/12/2012</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]

For each unique contract, region and QTR end there will only ever be one 'Number'.

E.g For contract BBB, in the North region for the QTR end 30/09/2012 the 'Number' = 3.

However, there may be duplicated lines per unique contract, region and QTR end where the differentiation is the 'Value'. In these cases the 'Number' will be duplicated.

What I'd like to be able to do (through a Dax Measure) is show the unique sum of the 'Number' for any contract per QTR end. This is probably best explained if I show the desired result:

[TABLE="width: 277"]
<TBODY>[TR]
[TD="colspan: 2"]Desired Result</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Contract</SPAN>
[/TD]
[TD="align: right"]30/09/2012</SPAN>
[/TD]
[TD="align: right"]31/12/2012</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]BBB</SPAN>
[/TD]
[TD]16</SPAN>
[/TD]
[TD]19</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Comprising</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]30/09/2012</SPAN>
[/TD]
[TD="align: right"]31/12/2012</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]West</SPAN>
[/TD]
[TD]5</SPAN>
[/TD]
[TD]5</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]North</SPAN>
[/TD]
[TD]3</SPAN>
[/TD]
[TD]4</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]East</SPAN>
[/TD]
[TD]2</SPAN>
[/TD]
[TD]1</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]South</SPAN>
[/TD]
[TD]6</SPAN>
[/TD]
[TD]9</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]


Thanks

 

[AlbertoFerrari]

Does something easy like SUMX (VALUES (Table[Number]), Table[Number])) help?

Alberto
www.sqllbi.com

 

[miguel.escobar]

if you need something more complicated like, if you want the MAX value per unique contract then you should try the SUMX combined with SUMMARIZE.

Hope this helps.

Miguel

 

[JEB85]

Hi,

I managed to get round the problem by removing duplicate values so that there was only ever one number per unique combination.

Should I encounter this problem again I will try out your solutions!

Thanks