Forum Discussion

123abc's avatar
123abc
Icon for Community Champion rankCommunity Champion
2 years ago

Need Formula for Calculate column

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

"Need Help"

1 Reply

  • Rupak_bi's avatar
    Rupak_bi
    Icon for Super User rankSuper User
    2 years ago

    are you still searching for the solution or its resolved?