topic Re: Calculate area under curve in DAX Commands and Tips

Calculate area under curve

Papillon — Fri, 08 Mar 2024 07:47:25 GMT

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.

Re: Calculate area under curve

talespin — Fri, 08 Mar 2024 09:14:24 GMT

hi Papillon

Can you please share pbix file with mock up data.

Re: Calculate area under curve

Papillon — Fri, 08 Mar 2024 09:24:06 GMT

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

Re: Calculate area under curve

talespin — Fri, 08 Mar 2024 09:45:12 GMT

hi Papillon

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

Re: Calculate area under curve

talespin — Fri, 08 Mar 2024 09:59:47 GMT

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

)

Re: Calculate area under curve

Papillon — Fri, 08 Mar 2024 10:07:08 GMT

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

Re: Calculate area under curve

Papillon — Tue, 12 Mar 2024 06:40:07 GMT

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

Re: Calculate area under curve

talespin — Tue, 12 Mar 2024 07:13:33 GMT

hi Papillon

You're welcome.