Forum Discussion

Jaskiratr's avatar
Jaskiratr
Regular Visitor
4 years ago
Solved

Find sum per unique value from another column

Hello!
So I have a sales order data set with multiple line items with different values even for the part number, as seen in the picture below. 

 

What I want to do is create a DAX measure that will sum up all values for a given part number, and then return the name of the highest value part. The issue is that the order data also contains negative values which are discounts. My original method of simply choosing the max value in the dataset only returns the part number with the highest value but does not consider the sum of the max value and discount combined. This is the measure I've written: 

Top Part = VAR MaxValue = MAX('Sharepoint BCA'[VALUE])
RETURN MAXX(FILTER('Sharepoint BCA', 'Sharepoint BCA'[VALUE] = MaxValue), 'Sharepoint BCA'[PARTNUMBER])
 
Can anyone help me correct this? I can provide more info if needed, thanks!
Need Help
  • davehus's avatar
    davehus
    4 years ago

    Hi, Try this

     

    Value_ Sum(Value)

     

    Place the below measure in a card visual to get what you need.

    Top Part =
    IF (
    NOT ( ISBLANK ( [Value_] ) ),
    MAXX (
    TOPN (
    1,
    SUMMARIZE (
    'Part Table',
    'Part Table'[#Part],
    "Sales Total", [Value_]
    ),
    [Sales Total]
    ),
    'Part Table'[#Part]
    )
    )
     
     

5 Replies

  • davehus's avatar
    davehus
    Memorable Member
    4 years ago

    Hi, Try this

     

    Value_ Sum(Value)

     

    Place the below measure in a card visual to get what you need.

    Top Part =
    IF (
    NOT ( ISBLANK ( [Value_] ) ),
    MAXX (
    TOPN (
    1,
    SUMMARIZE (
    'Part Table',
    'Part Table'[#Part],
    "Sales Total", [Value_]
    ),
    [Sales Total]
    ),
    'Part Table'[#Part]
    )
    )
     
     
    • Jaskiratr's avatar
      Jaskiratr
      Regular Visitor
      4 years ago

      Hello! Thanks for the response, I've tried using this measure but am coming up with a syntax error when trying to use it. The part number and the order value per line are located on the same table, as seen in my measure below. Also, sorry but this measure is to be used in a table where it displays a top part for each customer, not in a card format. Is there anything I need to change with this?

       

      • davehus's avatar
        davehus
        Memorable Member
        4 years ago

        You're sales total in the red should be followed by the [Sales Total]  measure. Same for the isblank clause at the start. Basically a virtual table is being created with the summarize measure. Take out Values(Sharepoint BCA... ) and replace with Sales Total. Also, this should work in a matrix report and will reflect for each customer. The card was only an example. 

  • davehus's avatar
    davehus
    Memorable Member
    4 years ago

    You're welcome, it's a very useful pattern to have. You can swap material for customer in summarize and it would give you the top customer by part in a matrix visual.