Forum Discussion

grega's avatar
grega
Helper II
3 years ago

Summarise Non Unique Values

Hi,

 

I have a problem that I hope someone will be able to help me with.

I need to summarise a table containing the Customer Name, Order Number, Stock Reference, Cost Price, Sale Price, and Margin Percentage.

 

If the Cost Price and the Sale Price are identical and there are multiple rows for the order I want to summarise it into one row by Order number.  Here is a data example:

 

Customer NameOrder NumberStock ReferenceCost PriceSale PriceMargin %
AB Smith2354KPCB_147681536.3758.76
AB Smith2354KPCB_147681536.3758.76

 

However if there is an order that has multiple rows but different Cost/Sale Prices I don't want to summarise that data.  Here is an example:

 

Customer NameOrder NumberStock ReferenceCost PriceSale PriceMargin %
T K Anderson2355BS10_5467417.532.9654.49
TK Anderson2355BS09_34889194052.5

 

I hope this makes sense.

 

Thanks for your help,

Greg.

10 Replies

  • FreemanZ's avatar
    FreemanZ
    Super User
    3 years ago

    hi grega 

    not sure what column do you want to summarize. Let me suppose you want the general Margin%, then try like:

    Margin% = 
VAR _table = 
    SUMMARIZE(
        TableName,
        TableName[Customer Name],
        TableName[Order Number],
        TableName[Stock Reference],
        TableName[Cost Price],
        TableName[Sale Price]
    )
RETURN
1 - 
DIVIDE(
    SUMX(_table, TableName[Cost Price]),
    SUMX(_table, TableName[Sale Price])
)

     

    • grega's avatar
      grega
      Helper II
      3 years ago

      Hi

       

      Thanks for the reply.

       

      I'm trying to summarise all the columns if multiple rows are itentical for an order.  So if you have multiple rows where the Cost Price, Sale Price and Margin % are the same, sumarise.  If there are orders where the Cost Price, Sale Price and Margin % on an order are different I don't want to summarise.  I hope that is clear.

       

      Thanks,

      Greg.   

      If there are