Sumx and Filter

[AllanBerces]

Hi good day can anyone help me to edit my calculated column, when i used the below parameter it shows all the value in each category. what i need is same parameter but addional  filter by Trade let say trade Plumber, Elect and Mech and also by shift nigth and Day.

Main_Reaming_hrs_shut = CALCULATE(

                            SUMX(MAIN, MAIN[Remaining hours]),

                                FILTER(

                                    ALL (MAIN),

                                        'MAIN'[Shutdown Mode] =EARLIER( MAIN[Shutdown Mode])

                                        && MAIN[Location] =EARLIER( MAIN[Location])))

Thank you

[rajendraongole1]

Hi @AllanBerces - create a calculated column as below to calculation only includes rows that match the same Shutdown Mode, Location, Trade, and Shift.
calculation only includes rows that match the same Shutdown Mode, Location, Trade, and Shift.

Main_Reaming_hrs_shut =
CALCULATE(
SUMX(
MAIN,
MAIN[Remaining hours]
),
FILTER(
ALL(MAIN),
MAIN[Shutdown Mode] = EARLIER(MAIN[Shutdown Mode]) &&
MAIN[Location] = EARLIER(MAIN[Location]) &&
MAIN[Trade] = EARLIER(MAIN[Trade]) &&
MAIN[Shift] = EARLIER(MAIN[Shift])
)
)

Hope it helps

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[AllanBerces]

Hi @rajendraongole1 thank you for your reply but the result is the same. for trade i have structural and welder what in need is to filter by Plumber, Elect and Mech, and same with the shift i have afternoon shift, nigth shift, midnigth shift and day shift and i want to filter by nigth and day only.

Thank you

[rajendraongole1 · Accepted solution]

Hi @AllanBerces -Understood. To filter by specific trades (Plumber, Elect, and Mech) and specific shifts (Night and Day), you need to explicitly include those conditions in filter function.

calculated column :

Main_Reaming_hrs_shut =
CALCULATE(
SUMX(
MAIN,
MAIN[Remaining hours]
),
FILTER(
ALL(MAIN),
MAIN[Shutdown Mode] = EARLIER(MAIN[Shutdown Mode]) &&
MAIN[Location] = EARLIER(MAIN[Location]) &&
MAIN[Trade] IN {"Plumber", "Elect", "Mech"} &&
MAIN[Shift] IN {"Night", "Day"}
)
)

Hope it helps. let me know if still issue exist.

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[AllanBerces]

HI @rajendraongole1 thank you very much work as i need.