Forum Discussion

Peter_2020's avatar
Peter_2020
Helper III
3 years ago
Solved

Specific column

Hi all, 

 

I would like to ask you for your help. I have one table where I have following data:

And what I need to do is use the filter for "OPERATION = "0090" AND "CODE"= "20" to get proper list of the PO and proper hours for specific PO. But when I use the filters I receive following data:

 

So in hours column is 0.00. Desired output should be 35.22. So I need to define some measure which calculate all hours for specific PO with 01 CODE and send it to row with operation 0090 and CODE 20...

 

Any idea how to do it?

Thank you in advance for your help. 

P. 

 

Need Help
  • v-jianboli-msft's avatar
    v-jianboli-msft
    3 years ago

    Hi Peter_2020 ,

     

    Sorry, I misunderstood before. If you are expecting it to show the sum of the hours for a specific PO, please try:

    Measure = CALCULATE(SUM('Table'[HOURS]),ALLEXCEPT('Table','Table'[PO]))

    Final output:

    Best Regards,

    Jianbo Li

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

7 Replies

  • AllisonKennedy's avatar
    AllisonKennedy
    Community Champion
    3 years ago

    Peter_2020 Why are you wanting to recode these values? You could use 'Replace Values' in Power Query, or duplicate the column first and then 'Replace Values' in power query, but we need to know more info on why to be able to provide the best answer for you.

    • Peter_2020's avatar
      Peter_2020
      Helper III
      3 years ago

      Hi AllisonKennedy , 

       

      because I need to know SUM of the hours for each specific PO´s that contains OPERATION "0090" and CODE "20". There are also PO´s that don´t have OPERATION "0090" and CODE "20" therefore I need to use this filter and somehow get SUM of the hours. 

      P. 

  • v-jianboli-msft's avatar
    v-jianboli-msft
    Community Support
    3 years ago

    Hi Peter_2020 ,

     

    Please try:

    Measure = 
var _a = SUMX(ALL('Table'[HOURS]),[HOURS])
var _b = SUM('Table'[HOURS])
return IF(ISINSCOPE('Table'[CODE]),_b,_a)

    Output:

     

    Best Regards,

    Jianbo Li

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Peter_2020's avatar
      Peter_2020
      Helper III
      3 years ago

      Hi v-jianboli-msft , 

      thanks for your reply. It works perfect when you have table with 1 PO. But in my case I have in the table around 1000 PO´s and then it return the sum for all hours in total:

      So the SUM of the hours must be defined for specific PO and must be in the same row, not in total.

      P. 

      • v-jianboli-msft's avatar
        v-jianboli-msft
        Community Support
        3 years ago

        Hi Peter_2020 ,

         

        Sorry, I misunderstood before. If you are expecting it to show the sum of the hours for a specific PO, please try:

        Measure = CALCULATE(SUM('Table'[HOURS]),ALLEXCEPT('Table','Table'[PO]))

        Final output:

        Best Regards,

        Jianbo Li

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.