Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

SUMX() for distances in miles

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! 

5 REPLIES 5
Bitwize_PowerBI
Advocate IV
Advocate IV

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
Not applicable

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
Not applicable

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?

Hi,

 

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

 

regards,

Dries

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.