SUMX() for distances in miles

[Anonymous]

Hello everyone!

I have a seemingly simple measure I'm trying to create. I have a table that includes a FROM latitude and longitude coordinates, and a TO latitude and longitude coordinates. Using the latitude and longitude, I'm attempting to calculate the distance between the two using using SUMX. Here is my formula:

Measure = 
SUMX([table],ACOS(COS(RADIANS(90-[FromLatitude]))) * 
SUMX([table],COS(RADIANS(90-[ToLatitude]))) + 
SUMX([table],SIN(RADIANS(90-[FromLatitude]))) * 
SUMX([table],SIN(RADIANS(90-[ToLatitude]))) *
SUMX([table],COS(RADIANS([FromLongitude]-[ToLongitude]))))*3959)

I have also created a calculated column for use with slicers. It works remarkably well. The only thing from the above equation that is different is I'm not using SUMX(). 

Column =
ACOS(COS(RADIANS(90-[FromLatitude]))) * 
COS(RADIANS(90-[ToLatitude]))) + 
SIN(RADIANS(90-[FromLatitude]))) * 
SIN(RADIANS(90-[ToLatitude]))) * 
COS(RADIANS([FromLongitude]-[ToLongitude]))))*3959)

Long story short, I'm getting incorrect results when using SUMX(). Since SUMX() calculates on a row level basis, should I not see the same results on each row of the measure as I do with calculated column? Or do I misunderstand the usage of SUMX(). Perhaps my syntax is wrong? 

Any guidance is greatly appreciated! 

[Bitwize_PowerBI]

Hi,

maybe you just want the sumx of your entire calculation expression:

SUMX([table],ACOS(COS(RADIANS(90-[FromLatitude]))) * 
COS(RADIANS(90-[ToLatitude]))) + 
SIN(RADIANS(90-[FromLatitude]))) * 
SIN(RADIANS(90-[ToLatitude]))) * 
COS(RADIANS([FromLongitude]-[ToLongitude]))))*3959))

and this measure on a row level should give you the same result as:

SUMX([table],[Column])

and should give the same result as your calculated column.

Are you sure you evaluate your measure for single rows ? 

[Anonymous]

Thank you for your reply. Using SUMX() for the entire expression seems to have got me closer! 

What I'm trying to do, is create a measure so I can total distances, average distances etc. across all our locations. I believe the best way for that using a SUM or SUMX. The calculated column for the distances works just fine for my filtering using slicers. Am I making any sense? 

[Anonymous]

I supect both of you are missing a CALCULATE( ) around your expression -- as to convert the row context from SUMX into the filter context for the expression?

[Bitwize_PowerBI]

Hi,

in a measure, the CALCULATE() is implicit (around the expression), so you don't have to explicitely write it.

regards,

Dries

[KHorseman]

@Bitwize_PowerBIyou have it sort of backward. If you reference a measure, the CALCULATE is implicit. It's not that the expression is in a measure. If that were the case we would never have to use CALCULATE in any measure. If you just stick an expression in a SUMX rather than a reference to another measure, there is no implicit CALCULATE. A naked expression needs a CALCULATE in the construction you're using.