cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
stevedep
Super User I
Super User I

Sharing: DAX & Power BI file to calculate and display the Area of a Polygon.

Hi,

 

In this article, I would like to share the DAX code to calculate the area of a polygon. The below image shows the result:

stevedep_0-1613828582045.png

Here you see it in action:

poloygon.gif

 

This type of calculation is useful in case you would like to calculate the area of your selection. For example, in a GainChart. The bigger the area, the better. Using this calculation you can run it for groups of data. 

 

The code for the area calculation (in accordance with this source)

 

_AreaOfPolygon = 
VAR _Tbl =
UNION(
    ROW("X", [x1 Value], "Y", [y1 Value]),
    ROW("X", [x2 Value], "Y", [y2 Value]),
    ROW("X", [x3 Value], "Y", [y3 Value]))
VAR _TblWithRank = ADDCOLUMNS(_Tbl, "Rank", RANKX(_Tbl, SUM([X]), ,ASC,Skip))
VAR _x1 = CALCULATE(MINX(_TblWithRank, [X]), FILTER( _TblWithRank,[Rank]=1))
VAR _y1 = CALCULATE(MINX(_TblWithRank, [Y]), FILTER( _TblWithRank,[Rank]=1))
VAR _TblWithRankIncl = UNION(_TblWithRank, ROW("X", _x1, "Y", _y1, "Rank", COUNTROWS(_TblWithRank)+1))

VAR _TblInclMinRank =  SELECTCOLUMNS( ADDCOLUMNS(_TblWithRankIncl, "RankMin1", [Rank] -1), "XPrev", [X], "YPrev", [Y], "Rank", [RankMin1])

VAR _ResultTbl = NATURALINNERJOIN(_TblWithRank, _TblInclMinRank)

RETURN
ABS(
    SUMX(_ResultTbl,
        ([Y]*[XPrev]) - ([X]*[YPrev])
    )/2
 )

 

This is the code to create the dynamic SVG (mind this measure needs to be of the data category 'Image URL'). 

 

_SVG = 
------------SVG - start code & end code
VAR _SvgStart=
"data:image/svg+xml;charset=utf-8," &
"<svg xmlns='http://www.w3.org/2000/svg' x='0px' y='0px'
    width='150' 
    height='150' 
     viewBox = '0 0 150 150'> "
VAR _SvgEnd = " </svg>"
-------------------
VAR _Tbl =
UNION(
    ROW("X", [x1 Value], "Y", [y1 Value]),
    ROW("X", [x2 Value], "Y", [y2 Value]),
    ROW("X", [x3 Value], "Y", [y3 Value]))
VAR _TblWithRank = ADDCOLUMNS(_Tbl, "Rank", RANKX(_Tbl, SUM([X]), ,ASC,Dense))
VAR _x1 = CALCULATE(MINX(_TblWithRank, [X]), FILTER( _TblWithRank,[Rank]=1))
VAR _y1 = CALCULATE(MINX(_TblWithRank, [Y]), FILTER( _TblWithRank,[Rank]=1))
VAR _InnerPoints = CONCATENATEX(FILTER(_TblWithRank, [Rank]>1), [X] & "," & [Y] & " ")
VAR _SvgContent = " <polyline points='"&_x1&","&_y1& " " &_InnerPoints&_x1&","&_y1&"' style='fill:red;stroke:black;stroke-width:3' />"
// CONCATENATEX(_Tbl,[X] & "
// ")

RETURN
 _SvgStart&_SvgContent&_SvgEnd
// CONCATENATEX(_TblWithRank, [X] & " r:  " & [Rank] & "
// ")

 

The file is attached. 

Hope that you find it useful.

Kind regards, Steve. 

 

10 REPLIES 10
btsmith952
Advocate I
Advocate I

Awesome job! While testing if I could replicate this in R and Python I noticed the area's were slightly off and it looks like the formula for the _AreaOfPolygon was slightly, at least based on the formula in the link you provided. And honestly, I was only able to confirm this after putting the data into excel and running the formula there. The closure point (the forth point that closes the polygon) was returning the YPrev for the 3rd point which. I think the formula should be:

 

 

_AreaOfPolygon = 
VAR _Tbl =
UNION(
    ROW("X", [x1 Value], "Y", [y1 Value]),
    ROW("X", [x2 Value], "Y", [y2 Value]),
    ROW("X", [x3 Value], "Y", [y3 Value]))
VAR _TblWithRank = ADDCOLUMNS(_Tbl, "Rank", RANKX(_Tbl, [X], ,ASC,Skip))
VAR _x1 = CALCULATE(MINX(_TblWithRank, [X]), FILTER( _TblWithRank,[Rank]=1))
VAR _y1 = CALCULATE(MINX(_TblWithRank, [Y]), FILTER( _TblWithRank,[Rank]=1))
VAR _TblWithRankIncl = UNION(_TblWithRank, ROW("X", _x1, "Y", _y1, "Rank", COUNTROWS(_TblWithRank)+1))

VAR _TblInclMinRank =  SELECTCOLUMNS( ADDCOLUMNS(TblWithRankIncl, "RankMin1", [Rank]-1), "XPrev", [X], "YPrev", [Y], "Rank", [RankMin1])

VAR _ResultTbl = NATURALINNERJOIN(_TblWithRankIncl, _TblInclMinRank)

RETURN
ABS(
    SUMX(_ResultTbl,
        ([X]*[YPrev]) - ([Y]*[XPrev])
    )/2
 )

 

 

 Seriously though, this was an amazing post!

Just realized there's a much simpler formula for the area of this polygon haha.

 

_AreaOfPolygon = 
VAR a = SQRT(([x1 Value]-[x3 Value])^2+([y1 Value]-[y3 Value])^2)
VAR b = SQRT(([x2 Value]-[x1 Value])^2+([y2 Value]-[y1 Value])^2)
VAR c = SQRT(([x3 Value]-[x2 Value])^2+([y3 Value]-[y2 Value])^2)
VAR s = 0.5*(a+b+c)

RETURN
    SQRT((s*(s-a)*(s-b)*(s-c)))

 

Nice!

Should be said that it should be dynamic, in the sense that the number of data points can vary. I will share the use case regarding the calculation of the area of an gain chart later on. This allows the comparison of multiple predictive models.

 

Btw, your formula can also be applied in the current dynamic setup.

 

Thanks for the kind words btw 😀

Awesome! It was honestly such a minor thing, the only way I even was able to figure that out was stepping through the formula and adding each line one at a time. You really did an awesome job with the formula, that's a tricky formula in dax!

AntrikshSharma
Super User II
Super User II

@stevedep Nice! I would suggest you also post this on the blogs section to get more traction.

@AntrikshSharma , thanks! I have a requested @heather_italent to take a look, would be nice indeed if these posts (that I pre-fix with 'sharing: ' can go to the blog section. 

@stevedep  - I have just sent you a private message with information on blogging.  Thank you!

jameszhang0805
Resolver IV
Resolver IV

That's awesome! Thank you for sharing. Take my Kudos away.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors