Measure to sum beyond row context with condition

[Applicable88]

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. 

[PaulDBrown · Accepted solution]

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 

[V-pazhen-msft · Accepted solution]

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

[Applicable88]

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

Best regards.

[PaulDBrown · Accepted solution]

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 

[Applicable88]

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: 

https://drive.google.com/drive/folders/1Jjew0XnCbGKwSRS6lky-KESplyCxBtGw?usp=sharing

[vanessafvg]

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