cancel
Showing results for
Did you mean:
Helper III

## 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?

P.

1 ACCEPTED SOLUTION
Community Support

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 7
Community Support

Hi @Peter_2020 ,

``````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.

Helper III

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.

Community Support

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.

Helper III

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

P.

Helper III

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

Super User

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

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Helper III

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.