sumif based on different criteria in power bi desktop

[123abc]

Hi Experts:

i want to sumup Turnover data based on below different criteria (May measure or calculated column):

In my assignemtn there are 4 Tables (All tables are relational) :

1: Sales Turnvoer Table / Live Sales Journal invoice level entries.

2: Singed Turnvoer Table.  customer given with IDs and custoemr wise turnvoer amount given.

3: Item Master Table. like Refrigerator, Deep Freezer, Split AC, Microwave oven, Water Dispanser, Washing Machine.

4: Customer Master Table. 

Criterias:

1: 'Sales Table'[Invoice Date] >= DATE(2023, 8, 1)

2: 'Sales Table'[Invoice Date] <= DATE(2023, 12, 31)

3:  RELATED('ITEM MASTER'[FG_PRODUCT]) IN {"Ref_D/Cool" , "Deep Freezer", "Split AC"}

4: '5 MTO Signed'[NewKey] = 'Sales Table'[NewKey]

Requirement:

I wants to sum sales turnvoer of Refrigerator, Deep Freezer, Split AC from 1st Aug 23 to 31st Dec 23 only those customers whose have singed turnvoer amount.

i wants to present both dealers parallel that how much he signed turnover and how much he generate turnvoer in sales journal.

Already tried Measures:

 

Measure 1:

TargetedTurnover =

CALCULATE(

    SUM('Sales Table'[NetSales]),

    FILTER(

        'Sales Table',

        '5 MTO Signed'[NewKey] = 'Sales Table'[NewKey]

        && 'Sales Table'[Invoice Date] >= DATE(2023, 8, 1)

        && 'Sales Table'[Invoice Date] <= DATE(2023, 12, 31)

        && RELATED('ITEM MASTER'[FG_PRODUCT]) IN {"Ref_D/Cool" , "Deep Freezer", "Split AC"}

    )

)

Measure 2:

Turnover Aug-Dec 2023 =

VAR StartDate = DATE(2023, 8, 1)

VAR EndDate = DATE(2023, 12, 31)

VAR CustomerCode = '5 MTO Signed'[NewKey]

RETURN

    SUMX(

        FILTER(

            'Sales Table',

            'Sales Table'[NewKey] = '5 MTO Signed'[NewKey] &&

            'Sales Table'[Invoice Date] >= StartDate &&

            'Sales Table'[Invoice Date] <= EndDate &&

            RELATED('ITEM MASTER'[FG_PRODUCT]) IN {"Ref_D/Cool" , "Deep Freezer", "Split AC"}

        ),

        'Sales Table'[NetSales]

    )

Above measures are not shwo required results.

Sample pbix file is given 

https://drive.google.com/file/d/11AeFr96LfLGEIRuAlRY-HxEiTqJSEcKl/view?usp=sharing

[ERD]

Hi @123abc , the access to your file is restricted. Do you receive errors in your measures? From what I can see, you need some aggregation or SELECTEDVALUE for CustomerCode variable in the second measure.

[123abc]

Dear Sir,

Sorry for incovience now restricton on remove.

Sample Data.

Open Link for every one.

https://drive.google.com/file/d/11AeFr96LfLGEIRuAlRY-HxEiTqJSEcKl/view?usp=sharing

[ERD]

@123abc , from your file I don't see what's wrong and what is to be done. Besides,

• there's no '5 MTO Signed' table
• there's no 'Sales Table'[NewKey] column (as far as I understand [CustomerCode] is used instead)
• you already seem to have a calculated column with the expected result