cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Applicable88
Post Prodigy
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:

ProductProcessNoTime1Time2Time3
WWW1  0,25
WWW2   
WWW3   
WWW4 0,50,25
ZZZ1 1,50,5
ZZZ2   
ZZZ3   
ZZZ4 10,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
PaulDBrown
Super User
Super User

You should unpivot the Time columns in Power Query (name the Time references  column "TimeRef" and the Value colum "Time")

Fact Table.JPG

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:

result.JPG

I've attached a sample PBIX 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

V-pazhen-msft
Community Support
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])
Vpazhenmsft_1-1632814137521.png

 

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

5 REPLIES 5
V-pazhen-msft
Community Support
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])
Vpazhenmsft_1-1632814137521.png

 

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

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

Best regards.

 

PaulDBrown
Super User
Super User

You should unpivot the Time columns in Power Query (name the Time references  column "TimeRef" and the Value colum "Time")

Fact Table.JPG

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:

result.JPG

I've attached a sample PBIX 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

Applicable88
Post Prodigy
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

Applicable88_0-1632674303054.png

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
Super User
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
vanessafvg_0-1632557249534.png

 





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!




Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!