Sum of values with filter condition in measure

[romeop2]

Hi,

i need to add a measure in order to do a sum with a filter condition based on a value in the record, this is an example, in the last column there is the expected result:

full address	just street	just house number	families	if(isnum(just house number),1,0)	Expected value: sum of "families" where the full address of this row (with a clean house number) is contained in full address of all the other rows (with dirty house number)
My Street 10	My Street	10	3	1	15
My Street 10 S. B	-	-	0	-
My Street 10A	My Street	-	1	-
My Street 10B	My Street	-	8	-
My Street 10B2	-	-	0	-
My Street 10B12	My Street	-	1	-
My Street 10C	My Street	-	2	-
			15

[JandreKillianRIC]

Hi @romeop2 

If your house number is always the last digits and you dont have the field "just house number" in your data, Add this to your script 

IF(
Isnum(Mid([full address], Index([full address], ' ' , SubStringCount([full address], ' ')), Len([full address]))),
Mid([full address], Index([full address], ' ' , SubStringCount([full address], ' ')), Len([full address])), 0) as HouseNumCorrect;

Then in your table you will need to get all the ones that is incorrect and also add the correct one (I see your total is 15 which includes the correct house number families too. This should be the measure on your table

IF(HouseNumCorrect <> 0, Sum(Total {<HouseNumCorrect = {0}>} Fam) + Sum(Fam))

Regards - Jandre

[romeop2]

Hi Jandre,

the expected result of families should be 15 in the row with the correct number. Furthermore, i have thousands of addresses and i need to filter the result sum also with the street name, this is just a subset of rows.

Many thanks

Romeo

[JandreKillianRIC]

Hi @romeop2 

Ok if you have the street name in a separate field you can just use that. If you don't add this to your script. 

Trim(Left([full address], FindOneOf([full address], '1234567890') -1)) as StreetName

And then just adjust your measure to this. 

IF(HouseNumCorrect <> 0, Sum(Total <StreetName> {<HouseNumCorrect = {0}>} Fam) + Sum(Fam))

I am using different values - So mine is not 15. But it works as expected - Please see below 

Regards - Jandre

[romeop2]

Hi @JandreKillianRIC ,

in StreetName you should add also the correct house number, like 10 for all the MyStreet or for YourStreet number 13 or 15 and so on, the sum should be done for example for "MyStreet 10".

Many thanks