Forum Discussion

TomSinAA's avatar
TomSinAA
Icon for Helper IV rankHelper IV
6 years ago
Solved

Select distinct to get cost

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

DeliverItemDelivery Cost
A1$1
A2$1
A3$1
A4$1
A5$1
B6$20
B7$20
B8$5
C9$5

 

So the measure should show the cost as:

A=$1

B=$20

C=$5

Need Help
  • v-chuncz-msft's avatar
    v-chuncz-msft
    6 years ago

    TomSinAA 

     

    You may try the measure below.

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

     

7 Replies

  • MartynRamsden's avatar
    MartynRamsden
    Icon for Solution Sage rankSolution Sage
    6 years ago

    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.

      • MartynRamsden's avatar
        MartynRamsden
        Icon for Solution Sage rankSolution Sage
        6 years ago

        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. 

  • v-chuncz-msft's avatar
    v-chuncz-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    TomSinAA 

     

    You may try the measure below.

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

     

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    6 years ago

    Hi,

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

    • TomSinAA's avatar
      TomSinAA
      Icon for Helper IV rankHelper IV
      6 years ago

      Yes, my mistake. It should be 20

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        6 years ago

        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.