calculation line number correct but not on total

[Anonymous]

hi,

have created an measure, qty * salesprice which works on orderline level but if the order has multiple lines i get wrong results:

Ordernr  Orderline  QTY  Salesprice  Total

  100           1            10         50          500

  100           2              5         40          200

  100           3              6         60          360

So, for ordernr 100, we have sold 21 pcs with a revenue of 1060 but due to the fact that the total Salesprice=150, we have instead of 1060 revenue, 3150.... You could say, do not summarize the Salesprice but we need this info for analysis purposes! 

Also tried to use a calculation like this, but it is also not working properly:

TEST=

VAR ORDERLINENUMER = SELECTEDVALUE(Fact_Sales[Orderline nr])
VAR ORDERNUMBER = SELECTEDVALUE(Fact_Sales[Order Number])

RETURN
CALCULATE( Fact_Sales[InvoicedQty]*fact_sales[Total Sales price no discounts], KEEPFILTERS(ORDERLINENUMER = Fact_Sales[Orderline nr]), KEEPFILTERS(ORDERNUMBER=Fact_Sales[Order Number]))

any ideas???

Thanks in advance!!!

Maarten

[wdx223_Daniel · Accepted solution]

sumx(fact_sales,Fact_Sales[InvoicedQty]*fact_sales[Total Sales price no discounts])

??

[Anonymous]

hi Daniel,

was too enthousiastic by your solution, unfortunately it is not working properly as well.... 😞

with the suggested sumx statement only half the value is presented. F.e. if you take the 3rd line in my example then instead of having 360 as result, it will now give me 180.....