Specific column

[Peter_2020]

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. 

[v-jianboli-msft]

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]

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 · Accepted solution]

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.

[Peter_2020]

Hi @v-jianboli-msft , 

one more request:

is it possible to adjust the formula to get proper sum of the hours with operation filter?:

it means that when I set filter for operation 0050 I get just the sum of the hours for specific operation - in this case 3,6 hours, for operation 0070 - 2,58; 0080 - 6,9 and so on....

so not the sum of all hours but just for specific operation...

Thank you in advance. 

P. 

[Peter_2020]

Hi @v-jianboli-msft , 

excellent! That's exactly what I needed. Thank you a lot for your help!

[AllisonKennedy]

@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]

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.