Select distinct to get cost

[TomSinAA]

I am trying to create a measure to get the cost for each delivery. My table looks like:

Deliver	Item	Delivery Cost
A	1	$1
A	2	$1
A	3	$1
A	4	$1
A	5	$1
B	6	$20
B	7	$20
B	8	$5
C	9	$5

The measure should show the cost as:

A = $1

B = $20

C=$5

[TomSinAA]

Hello,  I have a table wit deliveries and items for each delivery along with a cost for each delivery.  How can I create a measure to get the cost for each distinct delivery. My data looks like this:

Deliver	Item	Delivery Cost
A	1	$1
A	2	$1
A	3	$1
A	4	$1
A	5	$1
B	6	$20
B	7	$20
B	8	$5
C	9	$5

So the measure for the cost for each delivery should be:

A = $1

B = $20

C = $5

[v-lid-msft]

Hi @TomSinAA ,

We can use the following measure in visual to meet your requirement, it will calculate the cost for different divery in visual.

Cost Measure = MAX('Table'[Delivery Cost])

If it doesn't meet your requirement, kindly share your sample data and expected result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

Best regards,

[d_gosbell]

One option would be to use a measure like the following

Total Delivery Cost = 
var _costs = SUMMARIZE('Table','Table'[Deliver],'Table'[Delivery Cost])
return sumx(_costs,[Delivery Cost])

The other way of doing this would be to split the item level data into one table and the delivery based data into another table as it looks like you have data of mixed grain in this table which is what causes issues like this. This will complicate your data load and require you to build a proper star schema with dimension tables linked to each of the different fact tables, but it will simplify and speed up measures like this.  

[VasTg · Accepted solution]

@TomSinAA 

Did you try max function?

Measure = Max(Table[Delivery Cost])

OR

Choose Maximum in the dropdown.

If this helps, mark it as a solution.

Kudos are nice too