cancel
Showing results for
Search instead for
Did you mean:
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:

Here you see it in action:

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&colon;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.

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

Proud to be a Super User!

10 REPLIES 10
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!

Super User I

@btsmith952 ,
Thanks so much! I will look into your adjustment asap and change the code accordingly.

btw, its a blog post now

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

Proud to be a Super User!

Advocate I

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)))``````

Super User I

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 😀

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

Proud to be a Super User!

Advocate I

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!

Super User II

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

Thank you,
Antriksh Sharma
Super User I

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

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

Proud to be a Super User!

Administrator

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

Resolver IV

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

Super User I

Glad to read!

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

Proud to be a Super User!

## Helpful resources

Announcements

#### Welcome to the User Group Public Preview

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

#### Check it Out!

Click here to read more about the April 2021 Updates!

#### Sign up for our May 4th event!

May the fourth be with you, join us online!

#### 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
Top Kudoed Authors