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

[stevedep]

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

 

[Anonymous]

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!

[stevedep]

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

btw, its a blog post now 

https://community.powerbi.com/t5/Community-Blog/DAX-amp-Power-BI-file-to-calculate-and-display-the-Area-of-a/ba-p/1681485 

[Anonymous]

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

[stevedep]

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 😀

[Anonymous]

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]

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

[stevedep]

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

[heaher_iTalent]

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

[jameszhang0805]

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

[stevedep]

Glad to read!