Calculate sum of values between specific rows

[Anonymous]

I'm stuck trying to figure out how to solve this problem and I really hope you can help me.

I have two tables :

• Code Fusion

Site	Line	Element position	Element	Campaign	Code
GRA1	ABP025	1	TUBE	2	GRA1ABP0251TUBE2
GRA1	ABP025	1	TUBE	6	GRA1ABP0251TUBE6
GRA1	ABP025	3	COUD	3	GRA1ABP0253COUD3
GRA1	ABP025	3	COUD	7	GRA1ABP0253COUD7
GRA1	ACO003	3	TUBE	1	GRA1ACO0033TUBE1
GRA1	ACO003	3	TUBE	5	GRA1ACO0033TUBE5
GRA1	ACO003	5	REUC	4	GRA1ACO0035REUC4
GRA1	ACO003	5	REUC	7	GRA1ACO0035REUC7

• Campaign duration

Site	Line	Element position	Element	Campaign	Code	Campaign duration (h)
GRA1	ABP025	1	TUBE	1	GRA1ABP0251TUBE1	6584
GRA1	ABP025	1	TUBE	2	GRA1ABP0251TUBE2	6958
GRA1	ABP025	1	TUBE	3	GRA1ABP0251TUBE3	6523
GRA1	ABP025	1	TUBE	4	GRA1ABP0251TUBE4	6215
GRA1	ABP025	1	TUBE	5	GRA1ABP0251TUBE5	7115
GRA1	ABP025	1	TUBE	6	GRA1ABP0251TUBE6	4885
GRA1	ABP025	1	TUBE	7	GRA1ABP0251TUBE7	6523
GRA1	ABP025	1	TUBE	8	GRA1ABP0251TUBE8	6654
GRA1	ABP025	3	COUD	1	GRA1ABP0253COUD1	6656
GRA1	ABP025	3	COUD	2	GRA1ABP0253COUD2	7154
GRA1	ABP025	3	COUD	3	GRA1ABP0253COUD3	2656
GRA1	ABP025	3	COUD	4	GRA1ABP0253COUD4	9845
GRA1	ABP025	3	COUD	5	GRA1ABP0253COUD5	6225
GRA1	ABP025	3	COUD	6	GRA1ABP0253COUD6	6944
GRA1	ABP025	3	COUD	7	GRA1ABP0253COUD7	2354
GRA1	ACO003	3	TUBE	1	GRA1ACO0033TUBE1	6644
GRA1	ACO003	3	TUBE	2	GRA1ACO0033TUBE2	6666
GRA1	ACO003	3	TUBE	3	GRA1ACO0033TUBE3	6774
GRA1	ACO003	3	TUBE	4	GRA1ACO0033TUBE4	6885
GRA1	ACO003	3	TUBE	5	GRA1ACO0033TUBE5	6445
GRA2	ACO004	3	TUBE	6	GRA2ACO0043TUBE6	8554
GRA1	ACO003	5	REUC	1	GRA1ACO0035REUC1	2466
GRA1	ACO003	5	REUC	2	GRA1ACO0035REUC2	5484
GRA1	ACO003	5	REUC	3	GRA1ACO0035REUC3	3524
GRA1	ACO003	5	REUC	4	GRA1ACO0035REUC4	4965
GRA1	ACO003	5	REUC	5	GRA1ACO0035REUC5	4512
GRA1	ACO003	5	REUC	6	GRA1ACO0035REUC6	6577
GRA1	ACO003	5	REUC	7	GRA1ACO0035REUC7	8454

The current relationship between the two table are:

Code Fusion          Campaign duration

Code               1:*   Code

The column Code is a column I created in Power Query by simply combining Site & Line & Element position & Element & Campaign. I want to calculate a sum of the [Campaign duration] between the campaigns that are present in 'Code Fusion' as I filter the site, the line and the element with slicers.

For example, when I filter with the slicers 'Code Fusion' [Site] = GRA1, 'Code Fusion' [Line] = ABP025 and 'Code Fusion' [Element] = TUBE, I would want to have a measure that will calculate the sum of of the campaign duration from campaign 2 to 6 ( = 6958 + 6523 + 6215 + 7115 + 4885 = 31696 )

But it gets tricky because since I have a one-to-many relationship, when I filter with 'Code Fusion' it will only keep the lines of the campaign 2 and 6 of TUBE in position 1 of ABP025 for example. I tried to do a measure like this : CALCULATE(SUM('Campaign duration'[Campaign duration)]), FILTER(ALL('Campaign duration'), 'Campaign duration'[Campaign] >= MIN('Code Fusion'[Campaign]) && 'Campaign duration'[Campaign duration] <= MAX('Code Fusion'[Campaign])))

But it will sum up all the campaigns from 2 to 6 from every site and every line and every element.

I hope my explanation is clear enough and there is a way out of this problem. Thank you in advance for all your help !

[jgeddes · Accepted solution]

If @tamerj1 s solution does not work you can try the measure as follows.

Duration =

var _site =

SELECTEDVALUE('Code Fusion'[Site])

var _line =

SELECTEDVALUE('Code Fusion'[Line])

var _element =

SELECTEDVALUE('Code Fusion'[Element])

var _codeTable =

ADDCOLUMNS('Code Fusion', "_code", RIGHT('Code Fusion'[Code],1))

var _lowerCampaign =

MINX(ADDCOLUMNS('Code Fusion', "_code", RIGHT('Code Fusion'[Code],1)),[_code])

var _upperCampaign =

MAXX(ADDCOLUMNS('Code Fusion', "_code", RIGHT('Code Fusion'[Code],1)),[_code])

return

CALCULATE(

    SUM('Campaign duration'[Campaign duration (h)]),

    FILTER(all('Campaign duration'), 'Campaign duration'[Site] = _site && 'Campaign duration'[Line] = _line && 'Campaign duration'[Element] = _element && 'Campaign duration'[Campaign] >= _lowerCampaign && 'Campaign duration'[Campaign] <= _upperCampaign)

)

[tamerj1]

Hi @Anonymous 
Thank you for this clear explanation. 
This can be solved if we work it the other way around. Actually I need Campaign duration table to filter Code Fusion not the opposite. Sounds strange? Maybe but this will provide a smooth solution to your problem. Please let me know if there are no other restrictions against this to decide whether to proceed or think of other solutions.

Thank you

[Anonymous]

Hi, thanks for replying ! Unfortunately the table Code Fusion is the one that lists the campaigns that we have other interesting data on and that we actually study. So if we filter with Campaign duration, it would be like going over 1000 types of campaigns without knowing which one has interesting data. I hope it's clear for you 🙂