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

     

     

     

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