Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## 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 Area column and I want to apply the formula on each value of Area (i.e Group By). The formula can be seen in the formula bar.

Any help on this will be appreciated.

Thanks,

Vivian.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

5 REPLIES 5
Anonymous
Not applicable

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

Super User

Hi,

For each Area, will the entries in the steps column have to be arranged in descending order (as i can observe in your example)?

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

@Ashish_Mathur  Yes they should be arranged in descending order.

Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Community Champion

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

Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

## Helpful resources

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Join our Community Sticker Challenge

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

#### Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors