Select distinct to get cost

[TomSinAA]

I am trying to create a measure to get the cost for each delivery. My data 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

So the measure should show the cost as:

A=$1

B=$20

C=$5

[Ashish_Mathur]

Hi,

For B, there are 2 unique costs - 20 and 5.  Why should the answer be 20?

[TomSinAA]

Yes, my mistake. It should be 20

[Ashish_Mathur]

Hi,

Drag Deliver to the Table visual and write these measures

Measure = MIN(Data[Delivery Cost])

Min delivery cost = SUMX(SUMMARIZE(VALUES(Data[Delivery]),VALUES(Data[Delivery],"ABCD",[Measure]),[ABCD])

Drag the second measure to the visual.

Hope this helps.

[v-chuncz-msft · Accepted solution]

@TomSinAA 

You may try the measure below.

Measure =
SUMX (
    VALUES ( 'Table'[Deliver] ),
    CALCULATE ( MAX ( 'Table'[Delivery Cost] ) )
)

[MartynRamsden]

Hi @TomSinAA 

Could you confirm if the Delivery Cost for row 8 of your data should be $20?

Best regards,

Martyn

If I answered your question, please help others by accepting it as a solution.

[TomSinAA]

Yes, it should be $20

[MartynRamsden]

Hi @v-chuncz-msft 

Try this:

Cost = 
SUMX (
    SUMMARIZE (
        Table1,
        Table1[Deliver],
        Table1[Delivery Cost]
    ),
    Table1[Delivery Cost]
)

Best regardsm,

Martyn

If I answered your question, please help others by accepting it as a solution.