the number and amount of issued orders... How?

[pani_victoria]

Hello!

Colleagues, the task is to calculate the number and amount of accepted orders and the number and amount of issued orders.

I have such a dataset

Date |Store |ID product |Sales |#doc |Name |#order |product
02.03.2025 |store A |14763 |10571,77 |2454 |Jane Ostin |374 |prepayment
02.03.2025 |store A |14763 |7065,6 |2462 |Jane Ostin |375 |prepayment
06.03.2025 |store A |14763 |12884,9 |2549 |Jane Ostin |393 |prepayment
07.03.2025 |store A |14763 |3688,8 |2573 |Jane Ostin |401 |prepayment
11.03.2025 |store A |12128 |11771,1 |358 |Jane Ostin |322 |Glasses to order
11.03.2025 |store A |14763 |-10005 |358 |Jane Ostin |322 |prepayment
11.03.2025 |store A |14763 |2760 |2627 |Jane Ostin |407 |prepayment
12.03.2025 |store A |14763 |-7065,6 |2657 | |375 |prepayment
31.03.2025 |store A |12128 |10571,77 |453 | |374 |Glasses to order
12.03.2025 |store A |12128 |7065,6 |2657 | |375 |Glasses to order
12.03.2025 |store A |14763 |-2760 |2647 |Jane Ostin |407 |prepayment
13.03.2025 |store A |12128 |16483,05 |2665 |Jane Ostin |355 |Glasses to order
13.03.2025 |store A |14763 |-9366,45 |2665 |Jane Ostin |355 |prepayment
16.03.2025 |store A |14763 |-12884,9 |2738 |Jane Ostin |393 |prepayment
16.03.2025 |store A |12128 |13145,6 |2738 |Jane Ostin |393 |Glasses to order
21.03.2025 |store A |12128 |3688,8 |2851 | |401 |Glasses to order
21.03.2025 |store A |14763 |-3688,8 |2851 | |401 |prepayment
25.03.2025 |store A |14763 |3128 |2775 |Jane Ostin |462 |prepayment
31.03.2025 |store A |14763 |-10571,77 |453 | |374 |prepayment
12.03.2025 |store A |12128 |9577,2 |2647 |Jane Ostin |407 |Glasses to order

The number and amount of accepted orders is calculated based on the accepted prepayment

Number of accepted orders = CALCULATE(DISTINCTCOUNT('dataset'[#order]),'dataset'[#order]<>0 && 'dataset'[ID product] = "14763" && 'dataset'[Sales]>0)

Amount of accepted orders = CALCULATE(sum('dataset'[Sales]),'dataset'[#order]<>0 && 'dataset'[ID product] = "14763" && 'dataset'[Sales]>0)

I get that Jane Ostin has accepted 6 orders for a total of 40099.07
#orders 374, 375, 393, 401, 407, 462

To calculate issued orders, we filter the dataset
#order <>0
product ID <> 14763
Sales >0

we get a table

Date |Store |ID product |Sales |#doc |Name |#order |product
11.03.2025 |store A |12128 |11771,1 |358 |Jane Ostin |322 |Glasses to order
31.03.2025 |store A |12128 |10571,77 |453 | |374 |Glasses to order
12.03.2025 |store A |12128 |7065,6 |2657 | |375 |Glasses to order
13.03.2025 |store A |12128 |16483,05 |2665 |Jane Ostin |355 |Glasses to order
16.03.2025 |store A |12128 |13145,6 |2738 |Jane Ostin |393 |Glasses to order
21.03.2025 |store A |12128 |3688,8 |2851 | |401 |Glasses to order
12.03.2025 |store A |12128 |9577,2 |2647 |Jane Ostin |407 |Glasses to order

This table includes orders that Jane Austen accepted - 5 rows, but the Name can be empty and 2 orders that Jane Austen issued - they are not in the accepted ones, but they have a Name. The #order 462 is not included in this list, it does not belong to issued.

Thus, there should be 7 orders for the amount of 72303.12

How can I calculate this?
I think it is good to use virtual tables, but I have no ideas

[v-sdhruv]

Hi @pani_victoria ,
Just wanted to check if you had the opportunity to review the suggestion provided?
If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank You

[v-sdhruv]

Hi @pani_victoria ,
Just wanted to check if you had the opportunity to review the suggestion provided?
If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank You

[v-sdhruv]

Hi @pani_victoria ,
Just wanted to check if you had the opportunity to review the suggestion provided?
If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank You

[v-sdhruv · Accepted solution]

Hi @pani_victoria ,
For accepted orders you can use a calculated table:

AcceptedOrders =
CALCULATETABLE (
VALUES ( 'dataset'[#order] ),
'dataset'[ID product] = 14763,
'dataset'[Sales] > 0,
'dataset'[#order] <> 0
)

As far as issued order is concerned, you can calculate it by
VAR IssuedByJane =
CALCULATETABLE (
VALUES ( 'dataset'[#order ] ),
'dataset'[ID product ] <> 14763,
'dataset'[Sales ] > 0,
'dataset'[#order ] <> 0,
'dataset'[Name ] = "Jane Ostin"
)

To remove any overlap with accepted orders :
IssuedOnly =EXCEPT ( IssuedByJane, AcceptedOrders )

Then Combine with accepted orders to get final list

FinalOrders =
UNION ( AcceptedOrders, IssuedOnly )

For issued order amount-
IssuedOrderAmount =
CALCULATE (
SUM ( 'dataset'[Sales] ),
FILTER ( 'dataset', 'dataset'[#order] IN FinalOrders )
)

For issued Order count

IssuedOrderCount =
CALCULATE (
DISTINCTCOUNT ( 'dataset'[#order] ),
FILTER ( 'dataset', 'dataset'[#order] IN FinalOrders )
)

Hope this helps!