cancel
Showing results for
Did you mean:  Post Prodigy

## Measure to sum beyond row context with condition

Hello,

summing values in the same row comes more natural, but now I have a problem where the values are always placed irregulary.

For instance that is my sample table:

 Product ProcessNo Time1 Time2 Time3 WWW 1 0,25 WWW 2 WWW 3 WWW 4 0,5 0,25 ZZZ 1 1,5 0,5 ZZZ 2 ZZZ 3 ZZZ 4 1 0,8

For example that is a time table for process planning. Each product needs several steps to be manufactured. In that case product "www" and "zzz "both need 4 steps to be build, but there other products which has less or more steps as well.

I have two different condition where and need two different measures:

Measure No1: sum the time in every row of Time2 and also the time in Time3 but only ProcessNo 1 and 4:

For product "www" I would get  0,5+0,25+0,25 =1

For product" zzz" I would get 1,5+1+0,5+0,8=3,8

Measure No2: sum for each Product only Time2 with the ProcessNo 4 and also Time3 ProcessNo. 1 and 4:

For product "www" I would get  0,5+0,25+0,25 =1

For product" zzz" I would get 1+0,5+0,8=2,3

Thank you very much in advance.

Best.

2 ACCEPTED SOLUTIONS  Super User

You should unpivot the Time columns in Power Query (name the Time references  column "TimeRef" and the Value colum "Time") Then create the following measures:

``````Measure1 =
CALCULATE (
SUM ( 'Table'[Time] ),
'Table'[ProcessNo] IN { 1, 4 },
'Table'[TimeRef] IN { "Time2", "Time3" }
)
``````
``````Measure2 =
VAR C1 =
CALCULATE (
SUM ( 'Table'[Time] ),
FILTER ( 'Table', 'Table'[ProcessNo] = 4 && 'Table'[TimeRef] = "Time2" )
)
VAR C2 =
CALCULATE (
SUM ( 'Table'[Time] ),
FILTER ( 'Table', 'Table'[ProcessNo] IN { 1, 4 } && 'Table'[TimeRef] = "Time3" )
)
RETURN
C1 + C2
``````

To get: I've attached a sample PBIX

In doing so, you are also helping me. Thank you!

Proud to be a Super User!  Community Support

@Applicable88

Thank you for sharing the sample pbix. In your scenario, you could just create 2 calculated columns in Table2 using Related() to get the expected sum time.

SumPrepTime = RELATED(Table1[PrepTime])
SumProdtime = RELATED(Table1[ProdTime]) Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

5 REPLIES 5  Community Support

@Applicable88

Thank you for sharing the sample pbix. In your scenario, you could just create 2 calculated columns in Table2 using Related() to get the expected sum time.

SumPrepTime = RELATED(Table1[PrepTime])
SumProdtime = RELATED(Table1[ProdTime]) Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.  Post Prodigy

@V-pazhen-msft I have to thank you:). Thats a very straightforward solution that didn't came to my mind.

Best regards.  Super User

You should unpivot the Time columns in Power Query (name the Time references  column "TimeRef" and the Value colum "Time") Then create the following measures:

``````Measure1 =
CALCULATE (
SUM ( 'Table'[Time] ),
'Table'[ProcessNo] IN { 1, 4 },
'Table'[TimeRef] IN { "Time2", "Time3" }
)
``````
``````Measure2 =
VAR C1 =
CALCULATE (
SUM ( 'Table'[Time] ),
FILTER ( 'Table', 'Table'[ProcessNo] = 4 && 'Table'[TimeRef] = "Time2" )
)
VAR C2 =
CALCULATE (
SUM ( 'Table'[Time] ),
FILTER ( 'Table', 'Table'[ProcessNo] IN { 1, 4 } && 'Table'[TimeRef] = "Time3" )
)
RETURN
C1 + C2
``````

To get: I've attached a sample PBIX

In doing so, you are also helping me. Thank you!

Proud to be a Super User!  Post Prodigy

Hello @vanessafvg ,

the filtering works, but in my example where I put the times already in one row cannot filter the right total values after selecting the materialID Instead of 6 and 2 there should be the sum of these orders : 12 and 4.

Since I dont exclude any rows in this example I took out the allexcept part of your measure.

But still it doesn't work.

If you can take a look it would be hightly appreciated. I changed Product to MaterialID:  Super User
give the following a try.

Measure 1 =
var time2 = CALCULATE(sum(Proceses[Time2]), ALLEXCEPT(Proceses,Proceses[Product]), Proceses[ProcessNo] in {1,4})
var time3 = CALCULATE(sum(Proceses[Time3]), ALLEXCEPT(Proceses,Proceses[Product]), Proceses[ProcessNo] in {1,4})
return time2 + time3
Measure 2 =
var time2 = CALCULATE(sum(Proceses[Time2]), ALLEXCEPT(Proceses,Proceses[Product]), Proceses[ProcessNo] = 4 )
var time3 = CALCULATE(sum(Proceses[Time3]), ALLEXCEPT(Proceses,Proceses[Product]), Proceses[ProcessNo] in {1,4})
return time2 + time3 If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!  