Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Create Calculated Table with customized formula (Simpson's Rule)

Hello,   I want to apply Simpson's rule on a table and create a new table to store the values. From the picture below, I have a TABLE and I want a calculated Table. There are multiple values in Are...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    Stachu  Ashish_Mathur  Thanks for the solutions. But I found an easy way to solve this problem. All I did was used Pivot feature in the edit query to Convert Rows of column "Steps" as columns with values as the column "value" in it.

    After doing this I got individual columns for each step, Table after pivoting looked like

     

    Area | step 0.02 | step 0.01| step 0.005 |...........

    A      |   0            |    0         |  0.6            |............

     

    then I just applied the formula using the calculated column.

Stachu's avatar
Stachu
Community Champion
7 years ago

in your original table add these 2 columns:

NextStep = 
VAR __Area = 'Table'[Area]
VAR __Step = 'Table'[Steps]
RETURN
CALCULATE(MAX('Table'[Steps]), FILTER(ALL('Table'), 'Table'[Area] = __Area && 'Table'[Steps]<__Step))
NextValue = 
VAR __Area = 'Table'[Area]
VAR __Step = 'Table'[NextStep]
RETURN
CALCULATE(MAX('Table'[Value]),FILTER(ALL('Table'),'Table'[Area] = __Area && 'Table'[Steps]= __Step))

then create a new table like this

Table2 =
GROUPBY (
    'Table',
    'Table'[Area],
    "Val", SUMX (
        CURRENTGROUP (),
        0.5 * ( [Value] + [NextValue] ) * ( [Steps] - [NextStep] )
    )
)