cancel
Showing results 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

Helper I

## Create a dynamic table with a calculated row

Create a new table from existing one with sliced data and add an additional row with calculations
a minute ago

hi

I have following table

Date                Col 1      Col 2     Col 3
01-01-2020    43566    100000   2
01-12-2020    23456    100000   3
30-06-2021    12345    100000   4
31-10-2022    43566    100000   2
30-11-2022    23456    100000   3
31-12-2022    12345    100000   4

User does the following :

Using Power Bi visual slicer , user keeps records only for year 2022 .

Following SHOULD HAPPEN

2) Automatically a dynamic table should be created as indicated below with an additonal last row with following calculations

2.1) Last row date should be latest date of the filtered data

2.2 ) COl 1 of Last row  should be (Value in col 1 of latest date * sum of col 3 of filtered rows )

Date             Col 1        Col 2        Col 3

31-10-2022  43566     100000      2
30-11-2022  23456     100000      3
31-12-2022  12345     100000      4
31-12-2022  12345*9

3 REPLIES 3
Community Support

Hi @ashwinkolte ,

Please refer to my pbix file.

``````let
Source = Table,
#"2022" = Table.SelectRows(Source, each Date.Year([Date])=2022),
Custom3 = Table.ToRecords(#"2022"),
MaxDate = List.Max(Source[Date]),
MaxCol1Value = Table.SelectRows(Source,each [Date]=MaxDate)[Col 1]{0},
Total = List.Sum(Table.SelectRows(Source, each Date.Year([Date])<>2022)[Col 3]),
Custom1 = Total,
Custom2 = {[Date = MaxDate,Col 1=MaxCol1Value*Total,Col 2=null,Col 3=null]},
Custom4 = Table.FromRecords(List.Combine({Custom3,Custom2}))
in
Custom4``````

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helper I

Dear  v-rongtiep-msft

Thanks for your response. However as mentioned in my requirement , I need a dynamic table to be created when a user slices data  on BI visual and depending on which records user selects  in table 1. Is that possible ?

Community Support

Hi @ashwinkolte ,

The dynamic addition of rows does not seem to be very feasible.

Best Regards
Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.

#### Power BI Monthly Update - June 2024

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

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors