Need Formula for Calculate column

[123abc]

sumif based on different criteria in power bi (Need Measure or calculated column).

‎08-30-2023 11:11 AM

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

[Rupak_bi]

are you still searching for the solution or its resolved?