Need Formula for Calculate column

123abc — Tue, 19 Sep 2023 12:40:13 GMT

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

‎08-30-2023 11:11 AM

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) :

Criterias:

Already tried Measures:

Measure 1:

Measure 2:

Above measures are not shwo required results.

Sample pbix file is given

Re: Need Formula for Calculate column

Rupak_bi — Fri, 10 Nov 2023 13:00:46 GMT

are you still searching for the solution or its resolved?

topic Re: Need Formula for Calculate column in DAX Commands and Tips

Need Formula for Calculate column

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.

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.

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"}

)

)

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]

)

Re: Need Formula for Calculate column