Don't sum orderlines

[Saimen]

Hello,

Got a question about plotting and calcutating orderlines to our members.
Case: we got orders coming in, those orders have orderlines and each orderline can be given to a different member.
Each orderline also becomes a status: (1 = accepted, 2 = denied, 4 = missed).

I've loaded the data and tested, but i concluded that it showed to much. I picked out an example:

Due to privacy i masked the members names in here.
This is one order with an article of 19,99 euro and one of 54,99 euro. In my source you can see the same orderline number and the state (1)

I created an calculation to show the accepted orderline amount:

Turnover_Accepted = CALCULATE(SUM(OrderAllocationArticleItem[Price]),OrderAllocationBundleLocation[State] = 1)

This is working but is showing to much, in this calculation i includes all the prices that are in the order, and not only the one of the article that have been located to the store of been picked up.
Example of how it shows this order now. It shows for both 2 stores 75 eur (total order amount) instead of 19,99 euro and one of 54,99 euro.


How can i change the DAX in order to make it work and it can recognize only the amount on the orderlines that are involved?

P.s. more data can be giving to provide a deeper insight of how its combined.

Thanks in advance!

[Icey]

Hi @Saimen ,

I guess that your two tables, OrderAllocationArticleItem and OrderAllocationBundleLocation, are connected by one column. And the relationship is 1-to-Many (single). If so, try this:

Measure =
CALCULATE (
    SUM ( OrderAllocationArticleItem[Price] ),
    OrderAllocationBundleLocation[State] = 1,
    CROSSFILTER ( OrderAllocationArticleItem[Column], OrderAllocationBundleLocation[Column], BOTH )
)

If the above measure doesn't work, please share me some sample data, not real data, for test.

Best regards

Icey

If this post helps, then consider Accepting it as the solution to help other members find it faster.

[Saimen]

Hello @Icey ,

Thanks for your reply, i apologize for my late response.
I tried our your dax suggestion but couldnt make it work so i spent some time on creating an example with dummy data.
File can be downloaded here: https://gofile.io/d/7uu51a

I got the source data from a reporting server, so the source is always the same.
Quick guide:

This is an webshop order, and it has 2 products in it.
You can see the whole order in OrderAllocation
An order will be assigned to a allocation step (in this case its step 4)
In OrderallocationBundle you can see that for the 2 products in the order, 2 bundles have been created.
And in OrderallocationBundleLocation you can see where these bundles have been shown so (which locations) and what state they got).
State 1 = accepted by that location, state 4 = declined by that location

So you can see that location 407 and location 457 picked up both one article from this order.
In the report you can see that the all got the total sum order the order assigned (75 eur), but that is not correct.
One article is 54,99 and one is 19,99. (see OrderAllocationArticle and OrderAllocationArticleItem)


I just keep on getting the total order amount instead of the accepted amount (of the article) at the shop. Think als the data source isn't working well, because of a missing relation between an Bundle and an Article.

Thanks again for helping.