sum if sum

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...

Need Help

Tutorial Requests

v-yanjiang-msft

3 years ago

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: