Forum Discussion

markcabantog's avatar
markcabantog
Helper I
3 years ago
Solved

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

 

Need Help
Tutorial Requests
  • v-yanjiang-msft's avatar
    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:

    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.

     

3 Replies

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Community Support
    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:

    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.