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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
walnei
Helper III
Helper III

calculated column

Hello , does anyone know any solution for this ?

I have a table (as print1 ) , 1 column dimension that has my KPis , and two métrias (Actual and Budget ) as print .   I would like to create a new dimension in the column kpi would be the kpi -2 / kpi -1 someone could help me how can I do this to make it as print2

Thank you!

 

 

 

print2apg.png

1 ACCEPTED SOLUTION
v-sihou-msft
Employee
Employee

@walnei

 

In this scenario, you want to insert a calculated row into your table. However, it's difficult to directly referece cell value for calculation in Power Query. I suggest you split your table into two tables, transpose the columns to calculate the result, then transpose back and merge queries. Please refer to steps below:

 

1. Split the Print 1 into two tables. 

1.PNG

2.PNG

 

2. Transpose the second table. 

6.PNG

 

3. Since we want to sum id and concatenate string, we can do error handling on row level. Just add calculated column like below:

 

= Table.AddColumn(#"Transposed Table", "Custom", each try [Column1]+[Column2] otherwise [Column2]&"/"&[Column1])

7.PNG

 

4. Transpose this table back.

8.PNG

 

5. Then transpose first table and add calculated column.

3.PNG

 

 

= Table.AddColumn(#"Transposed Table", "Custom", each [Column2]/[Column1])

4.PNG

 

 

6. Transpose it back and add an index column. 

5.PNG

 

7. Use "Merge Queries" to join the other table together, expand dimension column.

 

9.PNG

10.PNG

 

Regards,

View solution in original post

2 REPLIES 2
v-sihou-msft
Employee
Employee

@walnei

 

In this scenario, you want to insert a calculated row into your table. However, it's difficult to directly referece cell value for calculation in Power Query. I suggest you split your table into two tables, transpose the columns to calculate the result, then transpose back and merge queries. Please refer to steps below:

 

1. Split the Print 1 into two tables. 

1.PNG

2.PNG

 

2. Transpose the second table. 

6.PNG

 

3. Since we want to sum id and concatenate string, we can do error handling on row level. Just add calculated column like below:

 

= Table.AddColumn(#"Transposed Table", "Custom", each try [Column1]+[Column2] otherwise [Column2]&"/"&[Column1])

7.PNG

 

4. Transpose this table back.

8.PNG

 

5. Then transpose first table and add calculated column.

3.PNG

 

 

= Table.AddColumn(#"Transposed Table", "Custom", each [Column2]/[Column1])

4.PNG

 

 

6. Transpose it back and add an index column. 

5.PNG

 

7. Use "Merge Queries" to join the other table together, expand dimension column.

 

9.PNG

10.PNG

 

Regards,

@v-sihou-msft

 

Thanks, met for what I needed. It would be great to have a simpler way to do these calculations, as exists in other BI tools.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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