Do not want an aggregated total

[Anonymous]

Hello  -  Our ERP system spits out a column called "Total Price".    What is happening is that the total price is assigned to each line when it gets into Power Bi and ends up like this....which gives me incorrect totals.   Even if I remove the Order Line from the matrix, I still get the 5,042 total.     What I am trying to achieve is to have the correct total show, which should be the 840,420.    I've tried HASONEVALUE but with no luck.   

New Sum of Total Price = IF(HASONEVALUE(ShippedOrdersALL_Query[Order Number]),[Sum of Total Price],SUMX(VALUES(ShippedOrdersALL_Query[Order Number]),[Sum of Total Price]))

[Anonymous · Accepted solution]

@Greg_Deckler   Hi Greg  -  I worked out a simple solution that seems to work.   Unfortunately (and surprisingly!) your solution didn't work.    I called yours the New New Sum.   

But here is the one that worked:  

New Sum of Total Price = SUMX(VALUES(ShippedOrdersALL_Query[TotalPrice]),ShippedOrdersALL_Query[TotalPrice])

[Greg_Deckler]

@Anonymous Glad you got it! One nit-pick, I wouldn't use VALUES but rather DISTINCT. Unless you want that blank row that VALUES can return.

[Greg_Deckler]

@Anonymous Try this:

New Sum of Total Price = IF(HASONEVALUE(ShippedOrdersALL_Query[Order Number]),[Sum of Total Price],AVERAGEX(VALUES(ShippedOrdersALL_Query[Order Number]),[Sum of Total Price]))