sum if sum

[markcabantog]

can any one help me to write the script i want to get the result below

From Date Table and Data table

the rule is if sum of sales per week >=15 then it's 1, then i will need to sum the week based on period. 

i got this by if( sum(sales)>=15,1,0), but i don't know how to sum it again by period

[v-kalyj-msft · Accepted solution]

Hi @markcabantog ,

According to your description, here's my solution.

1.The two tables are related with Date column.

2.Create a calculated column in Data table.

WeekNo = RELATED('Date'[WeekNo])

2.Create a measure.

Measure =
VAR _T =
    ADDCOLUMNS (
        'Data',
        "Flag",
            IF (
                SUMX (
                    FILTER ( 'Data', 'Data'[WeekNo] = EARLIER ( 'Data'[WeekNo] ) ),
                    'Data'[Sales]
                ) > 15,
                1,
                0
            )
                / COUNTROWS (
                    FILTER (
                        'Data',
                        'Data'[Veg] = EARLIER ( 'Data'[Veg] )
                            && 'Data'[WeekNo] = EARLIER ( 'Data'[WeekNo] )
                    )
                )
    )
RETURN
    SUMX ( _T, [Flag] )

Get the correct result:

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Samarth_18]

Hi @markcabantog ,

You can directly write measure as below:-

Measure = IF(SUM('Table (2)'[sales])>= 15,1,0)

Output:-

Refer a file below:-

[markcabantog]

Hi @Samarth_18 , i get to this part, but i want the sum of weeks by period like this