Forum Discussion

AllanBerces's avatar
AllanBerces
Post Prodigy
2 years ago
Solved

Sumx and Filter

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
Need Help
  • rajendraongole1's avatar
    rajendraongole1
    2 years ago

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

     

     

     

4 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    2 years ago

    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's avatar
      AllanBerces
      Post Prodigy
      2 years ago

      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's avatar
        rajendraongole1
        Super User
        2 years ago

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