Forum Discussion

apoje's avatar
apoje
Helper II
6 years ago
Solved

Duplicate values in multiple rows - calculation problem

Hi,

 

ERP returns me a sales dataset that for each item in the order returns a row in a dataset, which specifies quantity, id and the rest... however this row also contains the total order value. So whenever I have an order with multiple items I get the rows populated with multiple duplicates of order totals. And therefore the sales report is insanely distorted. 

 

I am a novice in Power Pivot and my only way to solve this would be to create a duplicate dataset, which deletes all duplicates on the basis of OrderId and from there use the order total. 

But I think the more elegant solution would be with measures, however I do not know how to write it. Can any of you help me with this? 

The sample screenshot of the data is below:  

 

Thanks!

Andraz

  • I guess I'm going to solve it for myself :)

     

    MaxOrderAmount = MAX('Sharepoint-RAW-data-link'[OrderAmount.grossTotal])
    
    GrossTotalOrderAmountD = SUMX(DISTINCT('Sharepoint-RAW-data-link'[Order.id]),[MaxOrderAmount])

1 Reply

  • I guess I'm going to solve it for myself :)

     

    MaxOrderAmount = MAX('Sharepoint-RAW-data-link'[OrderAmount.grossTotal])
    
    GrossTotalOrderAmountD = SUMX(DISTINCT('Sharepoint-RAW-data-link'[Order.id]),[MaxOrderAmount])