DAX & Power BI file to calculate and display the A…

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.