Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
markcabantog
Helper I
Helper I

sum if sum

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

markcabantog_0-1669183569450.png

 

From Date Table and Data table

markcabantog_1-1669183590265.png

markcabantog_3-1669183665749.png

 

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. 

markcabantog_4-1669183987805.png

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

 

1 ACCEPTED SOLUTION
v-yanjiang-msft
Community Support
Community Support

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:

vkalyjmsft_0-1669276586661.png

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.

 

View solution in original post

3 REPLIES 3
v-yanjiang-msft
Community Support
Community Support

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:

vkalyjmsft_0-1669276586661.png

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

Hi @markcabantog ,

 

You can directly write measure as below:-

 

 

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

 

Output:-

image.png

Refer a file below:-

 

Best Regards,
Samarth

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin

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

 

markcabantog_0-1669187770735.png

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.