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 th...
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]
    )
    )
     
     
davehus's avatar
davehus
Icon for Memorable Member rankMemorable 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
      Icon for Memorable Member rankMemorable 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. 

      • Jaskiratr's avatar
        Jaskiratr
        Regular Visitor
        4 years ago

        Thank you! I used [Sales Total] = Sum(values) and it worked!