Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
sumx(fact_sales,Fact_Sales[InvoicedQty]*fact_sales[Total Sales price no discounts])
??
sumx(fact_sales,Fact_Sales[InvoicedQty]*fact_sales[Total Sales price no discounts])
??
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.....
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
18 |