Calculate area under curve

[Papillon]

Hi erverybody,

I'm struggeling with calculating the area underneath a curve in a stacked area chart. The curve is not following a function (like Integral calculus). The curve has bumps and dents. Base for the curve is a mesaure (not a specific column). 
Does anybody has an idea how to calculate the area?  Even a methode providing a close-to-real-result would be appreciated.
Thanks a lot!
P.S.: My goal is to compare serveral curves/areas by one key-figure. The area seems to be the most reasonable one.

 

[talespin · Accepted solution]

hi @Papillon 

Please see if this works for you. Just picking up hypothetical example.

I am using Trapezoid method to calculate ares under the curve, there is no unit. If you want to compare it to something, compare apples to apples, do not change dimension/measure when comparing to something.

I am using X-Axis(Age) - Y-Axis (SalesAmount).

Area Under Curve =

SUMX(

                VALUES(Customer[Age]),

                VAR _MinAge = CALCULATE(MIN(Customer[Age]), REMOVEFILTERS(Customer[Age]))

                VAR _MaxAge = CALCULATE(MAX(Customer[Age]), REMOVEFILTERS(Customer[Age]))

                VAR _SelectedAge = [Age]

                VAR _PreviousAge = CALCULATE(MAX(Customer[Age]), REMOVEFILTERS(Customer[Age]), Customer[Age] < _SelectedAge)

                VAR _SelectedY = CALCULATE([Sales Amount])

                VAR _PreviousY = CALCULATE([Sales Amount], REMOVEFILTERS(Customer[Age]), Customer[Age] = _PreviousAge)

                RETURN IF(_SelectedAge = _MinAge, 0, ((_PreviousY+_SelectedY)/2)*(_SelectedAge-_PreviousAge))

        )

[Papillon]

It works realy great for me! Thank you so much. I now, can easily compare different complex behavoiurs by just two figures!!!

[talespin]

hi @Papillon 

You're welcome.

[Papillon]

Wow! Great! I'm impressed how quick you had that! From scratch? From a first view it seems to be the solution.  I' ll check that out in detail by weekend. I'll come back to it! Thx so far....

[talespin]

hi @Papillon 

Please ignore, I am using Contoso, age and sales. will share my findings.

[talespin]

hi @Papillon 

Can you please share pbix file with mock up data.

[Papillon]

Hi @talespin ,

Thx for quick reply!

Hmm..... Understand!
As you may imagine, I'm not able to provide 1:1. I will check if I can setup easily a meaningful mock up. I'll be back. Regards