Calculate sales based on several exclusions

[T_JF2022]

Hello, I am now working with Power BI just under 4 months, and am not yet completely familiar with the different Dax formulas. So I hope someone here can help me with my project.
I have made the dataset available in google drive. Sample Help Desk – Google Drive

I want to calculate the Sales. (Column = OpenCoLinesNetAmoundCurr)

But I have to adjust this turnover on the basis of the following committees.

1. Sample Orderlines [Order No] is not listed in Invoiced 2021[Order Number]
2. Sample Orderlines [Order No] is not listed in Manual Adj [Order Number]
3. Sample Orderlines [Order Type] is SAO Sales Order (Other Order Types have to be removed)
4. Sample Orderlines [OrderLineOwnership] is Company Owned (Other Owned Status shouldnt be counted)
5. Sample Orderlines [CustomerNo] is not listed in Supplier [Supplier Number] and Sample Orderlines [Customer Name] is not listed in Supplier [Supplier Name]
6. IF Sample Orderlines [PartProductFamily] with "MC90" and Sample Orderlines [AccountingGRoupNo] with 10, has the Sample Orderlines [TotalCostBase] of 5000 or 10000 or 20000, then this complete Order shouldn't be counted.

It is Kinda complex, and I know it's probably impossible to do this with 1 Measure, but maybe someone here can help me to find the correct Measures in Dax? But actually, im lost in space and don't know how to start.

Bonus Question. Can I create a Measure which only Count the Amount for 1 Specific month?

Thank you for your Time & your help.

Best Regards

JF

[Greg_Deckler]

@T_JF2022 You can use DISTINCT('Invoiced 2021'[Order Number]) and similar to get your distinct values in those columns. Then you can use the IN operator to check if your Order No is in that list of values. You might consider creating a table VAR which does an ADDCOLUMNS on your table in your measure where you implement this complex logic and simply return a 1 or a 0 depending on whether you want to keep it. Then you can simply do a SUMX(FILTER(__Table,[Column] = 1),[OpenCoLinesNetAmoundCurr]) 

[T_JF2022]

@Greg_Deckler 

My BI level is beginner, and your solution sounds for me kinda complex?

Maybe I'm thinking too simply, but a non-pro level solution would be to simply do a calculation with the first condition.
The first result, let's call it intermediate result, I use further and determine a new result with the second condition and so on.
Not the cleanest solution, but can't something like this also lead me to the goal?

The question is then, what would the first query look like?

[Greg_Deckler]

@T_JF2022 Well, yes, complex because you have incredibly complex selection criteria! So, here is some sample psuedo-code:

Measure =
  VAR __Invoiced2021OrderNums = DISTINCT('Invoiced 2021'[Order Number])
  VAR __ManualAdjOrderNums = DISTINCT('Manual Adj'[Order Number])
  VAR __SupplierNums = DISTINCT('Supplier'[Supplier Number])
  VAR __SupplierNames = DISTINCT('Supplier'[Supplier Name])
  VAR __Table = 
    ADDCOLUMNS(
      FILTER(
        'Sample Orderlines',
        [Order Type] = "SAO Sales Order" && [OrderLineOwnership] = "Company Owned"
      ),
      "Include",
      SWITCH(TRUE(),
        NOT([Order No] IN __Invoiced2021OrderNums) && NOT([Order No] IN __ManualAdjOrderNums) && NOT([CusotmerNo] IN __SupplierNums) && NOT([Customer Name] IN __SupplierNames),1,0)
    )
RETURN
  SUMX(FILTER(__Table,[Include]=1), [OpenCoLinesNetAmountCurr])
      

5 out of 6 ain't bad. You'll have to add in the 6th selection criteria. You could do this in the SWITCH statement or in a FILTER clause for your SUMX.

[T_JF2022]

@Greg_Deckler 

First at all I've to say thank you for this Measure. 

Unfortunately, this intermediate state cannot be visualized. A comparison between values of type "text" & "integer" is not possible.
I understand the problem, have already tried to fix it in several places, but unfortunately have not come to any solution.
Attached the version with your stored measure.

https://drive.google.com/file/d/1i6Tv7-aUxf74a-eqQPgDOrSvOEEhbEHK/view?usp=sharing

Kind regards

[Greg_Deckler · Accepted solution]

@T_JF2022 No worries, super easy fix:

Greg_Deckler_Measure = 

Var _invoiced2021ordernums = distinct('Invoiced 2021'[Order Number])
Var _manualadj = distinct ('Manual Adj'[Order Number])
Var _suppliernums = distinct('Supplier'[Supplier Number])
Var _suppliernames = distinct ('Supplier'[Supplier Name])
Var _Table =

    ADDCOLUMNS(
        Filter ('Sample OrderLines', [OrderType]="SAO Sales Order" && [OrderLineOwnership]="Company Owned"), 
        "Include", 
        Switch(true(), 
        not([OrderNo] in _invoiced2021ordernums) && 
        not ([OrderNo] in _manualadj) && 
        not ([CustomerNo] & "" in _suppliernums) && 
        not ([Customer Name] in _suppliernames), 1,0)) 
        
        return 
sumx(Filter (_Table, [Include]=1), [Open CO Lines NetAmountCurr])

[T_JF2022]

@Greg_Deckler Im thankfull for your Input. Point 5 & 6 will be figured out with some adj. in the Dataset to make it way more easy to address. 

I am still in my initial steps on the long road to learning power bi. Therefore, exchanges like these are not only purposeful, but enrich my knowledge immensely. Big thank you.

Kind Regards